A lot of Data Marts or reporting databases will include a Calendar or Dates table to list all days within a certain range, along with data on those days (Day of the week, quarter, etc.).
I’ve posted my script on GitHub that will generate these records.
At a previous job, we wanted to track days the office was closed so that we could measure how many business days certain functions took. Determining weekends was easy, but calculating holidays took a little more effort. You could just manually enter the holidays for each year, but it is possible to calculate the holidays for each year in one pass.
Some holidays are the same date every year, like Christmas.
UPDATE dbo.Calendar SET IsHoliday = 1 WHERE [Month] = 12 AND [Day] = 25;
Others are on a specific day of the week, like Labor Day being the first Monday in September (This script is marking US holidays).
UPDATE c SET IsHoliday = 1 FROM dbo.Calendar as c JOIN ( SELECT DateKey, ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY DateKey) AS RowNumber FROM dbo.Calendar WHERE [Month] = 9 AND [WeekDay] = 2 ) AS z ON z.DateKey = c.DateKey AND z.RowNumber = 1;
This statement will find the first Monday of each September for the range of dates in the table, and mark it as a holiday.
Another method I saw used was to look for a Monday in September where the day was between 1 and 7, since the first Monday of the month will always be in that range.
Of course, the hardest holiday to calculate is Easter. Luckily, I found an algorithm on the US Naval Observatory site to determine the date. I’ve included a function in the Calendar script to calculate the date.
Another thing to consider (which I didn’t include in this script) is if a holiday falls on a weekend, a business may choose the nearest weekday to observe that holiday.