Introduction

In the previous article about creating a Calendar table, please recall that the Calendar table was generated from a tally table. That article provided code for a view (vTally) that supplies large numbers of sequentially numbered rows on demand. You may also recall that a very abbreviated holidays table was used to flag dates as holidays, rather than workdays.

Surely there were some groans over the suggestion that the holidays could be entered manually. Let’s face it. We want to create a table with data for years in advance and be done with it.

Before beginning, please note: The examples that follow are just examples, not comprehensive. Please forgive me for not including Easter, Yom Kippur, Diwali, Ramadan, Canadian Thanksgiving Day, Hanukkah, Bastille Day, Kwanzaa, Boxing Day, Cinco de Mayo, Festivus, or any other national holiday, holy day, or holy season you are especially fond of. Please don’t be offended by the omission of your favorite holiday from the examples of holidays omitted. And finally, please don’t read anything into the sequence of the list of omitted holidays. I thank you. Now back to business

Holidays Table: Fixed-date holidays

Holidays come in two flavors, those that occur on the same date every year (In America: New Year’s Day, Independence Day, Christmas Day) and those that occur on varying dates. (In America:  Memorial Day, Labor Day, Thanksgiving Day).

To generate a list of holidays that occur on the same day is fairly easy. Just list them in a VALUES clause, then use vTally and DATEADD(YEAR) to increment the years in which they occur. If we want to generate 100 years’ worth of data for three holidays, we need to generate 300 rows.

SELECT TOP(300) 
       DATEADD(YEAR,N-1,CONVERT(date,v.HolidayDate)) as HolidayDate
       ,HolidayName
FROM dbo.vTally t
CROSS JOIN (VALUES
                ('1/1/2000','New Years Day')
              , ('7/4/2000','Independence Day')
              , ('12/25/2000', 'Christmas Day')                           
              ) as v (HolidayDate, HolidayName);

Variable-date Holidays

Holidays with variable dates are slightly trickier. In America:

•     Memorial Day is celebrated on the last Monday in May.
•     Labor Day falls on the first Monday in September.
•     Thanksgiving falls on the fourth Thursday in November.
•     Easter... we will not attempt at this time.

If we return to the Calendar table from the previous article, we will find two columns to help us identify these variable dates. First, the [DoWAsc] column tells us whether a given day of the week is the first, second, third, fourth, or fifth occurrence of that day in a given month. Second, the [LastDoWInMonth] flag indicates whether or not a given date is the last occurrence of that weekday in a given month.

The following code was adapted from the previous article. In it, the “Dates” CTE in the following code generates a basic list of dates. Then “Expansion_1” applies the DATEPART function, using different parameters to produce various convenient grouping columns. After that “Expansion_2” develops the special columns needed to identify the variable-date holidays.

Then a SELECT takes the output from “Expansion_2” and applies a very different looking Values clause.

SELECT CalendarDate as HolidayDate, HolidayName
FROM Expansion2 x2
CROSS APPLY (VALUES   
(CASE WHEN MonthNo =  5 and [DayOfWeek] = 2
           and LastDoWInMonth = 1
      THEN 'Memorial Day' ELSE NULL END)
                            
,(CASE WHEN MonthNo =  9 and [DayofWeek] = 2
            and [DoWAsc] = 1
       THEN 'Labor Day' ELSE NULL END)
                     
,(CASE WHEN MonthNo = 11 and [DayofWeek] = 5
            and [DoWAsc] = 4
       THEN 'Thanksgiving Day'ELSE NULL END)
 ) v (HolidayName)
WHERE HolidayName is not null
  And CalendarDate < '1/1/2100'
ORDER BY CalendarDate

Remember, the VALUES clause can contain expressions, not just constants. In this instance, the code has CASE expressions which test the month, day of week, and one of two special columns to assign a HolidayName.

To find Memorial Days, the code tests for MonthNo = 5 (May) and [DayOfWeek] = 2 (Monday). If the row is the last Monday in May (LastDoWInMonth = 1) it’s a Memorial Day.  

Similarly, Labor Days are the 1^st Monday in September, (MonthNo = 9, [DayOfWeek] = 2 and DoWAsc = 1

Finally, Thanksgivings fall on the 4^th Thursday in November (MonthNo = 11, [DayOfWeek] = 5 and DoWAsc = 4).

Conclusion

The final code (attached) combines the queries for both the fixed-date and variable-date holidays with a UNION ALL, then inserts the results into a table. Join to this table during the generation of a Calendar table to drastically reduce the job of flagging Calendar dates as holidays. If you are not American, simply replace the two VALUES clauses with the dates and tests for your own holidays.