Populating U.S. Federal Holidays in a Calendar Table

The title of this blog is only half as thrilling as the SQL. I can assure you. I have a calendar table at work that is based off of the calendar table that you can find on aspfaq.com in an article titled Why should I consider using an auxiliary calendar table? The big problem is that the holidays in the script are not actually federal holidays. They’re just a set of holidays that seemed significant to the author at the time of writing. The Federal government actually has rules about how tocalculate holidays as well as a great list of federal holidays. One of the more interesting rules is that if a holiday falls on a Saturday, the holiday is observed on a Friday. If the holiday falls on Sunday, the holiday is observed on Monday. That means New Year’s Day sometimes happens last year. The following query is pretty self-explanatory and basically uses a few look forwards and look backs to figure out when holidays end up falling on the weekend and it makes a second, observed, holiday on the closest weekday. I have verified this from 1999 through 2020 just to make sure that my math was correct against all posted Federal Holidays. I hope this saves somebody some time.``` UPDATE dbo.Calendar SET IsHoliday = 0;

– New Year’s day UPDATE Calendar SET IsHoliday = 1 WHERE M = 1 AND D = 1;

– New Year’s day observed – if NYD is a Saturday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 12 AND c1.D = 31 AND c1.DW = 6 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 1 AND c2.D = 1 AND c2.[Date] > c1.[Date] AND DW = 7);

– if NYD is a Sunday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 1 AND c1.D = 2 AND c1.DW = 2 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 1 AND c2.D = 1 AND c2.[Date] < c1.[Date] AND c2.DW = 1);

– Martin Luther King Day UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 1 AND c1.DW = 2 AND (SELECT COUNT(*) FROM Calendar AS c2 WHERE c2.M = 1 AND c2.DW = 2 AND c2.Y = c1.Y AND c2.[Date] < c1.[Date]) = 2;

– Washington’s Birthday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 2 AND c1.DW = 2 AND (SELECT COUNT(*) FROM Calendar AS c2 WHERE c2.M = 2 AND c2.DW = 2 AND c2.Y = c1.Y AND c2.[Date] < c1.[Date]) = 2;

– Memorial Day UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE M = 5 AND DW = 2 AND NOT EXISTS (SELECT 1 FROM Calendar AS c2 WHERE M = 5 AND DW = 2 AND c2.Y = c1.Y AND c2.[Date] > c1.[Date]);

– Independence Day UPDATE Calendar SET IsHoliday = 1 WHERE M = 7 AND D = 4;

– Independence Day, observed – if Independence Day is a Saturday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 7 AND c1.D = 3 AND c1.DW = 6 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 7 AND c2.D = 4 AND c2.DW = 7 AND c2.[Date] > c1.[Date]);

– if Independence Day is a Sunday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 7 AND c1.D = 5 AND c1.DW = 2 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 7 AND c2.D = 4 AND c2.DW = 1 AND c2.[Date] < c1.[Date]);

– Labor Day UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE M = 9 AND DW = 2 AND NOT EXISTS (SELECT 1 FROM Calendar AS c2 WHERE M = 9 AND DW = 2 AND c2.Y = c1.Y AND c2.[Date] < c1.[Date]);

– Columbus Day UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 10 AND c1.DW = 2 AND (SELECT COUNT(*) FROM Calendar AS c2 WHERE c2.M = 10 AND c2.DW = 2 AND c2.Y = c1.Y AND c1.[Date] > c2.[Date]) = 1

– Veterans’ Day UPDATE Calendar SET IsHoliday = 1 WHERE M = 11 AND D = 11

– Veterans’ Day, observed – when Veterans’ Day is a Saturday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 11 AND c1.D = 10 AND c1.DW = 6 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 11 AND c2.D = 11 AND c2.DW = 7 AND c2.[Date] > c1.[Date]);

– when Veterans’ Day is a Sunday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 11 AND c1.D = 12 AND c1.DW = 2 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 11 AND c2.D = 11 AND c2.DW = 1 AND c2.[Date] < c1.[Date]);

– Thanksgiving Day UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE M = 11 AND DW = 5 AND (SELECT COUNT(*) FROM Calendar AS c2 WHERE M = 11 AND DW = 5 AND c2.Y = c1.Y AND c2.[Date] < c1.[Date]) = 3;

– Christmas Day UPDATE Calendar SET IsHoliday = 1 WHERE M = 12 AND D = 25;

– Christmas Day, observed – when Christmas Day is a Saturday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 12 AND c1.D = 24 AND c1.DW = 6 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 12 AND c2.D = 25 AND c2.DW = 7 AND c2.[Date] > c1.[Date]);

– when Christmas Day is a Sunday UPDATE Calendar SET IsHoliday = 1 FROM Calendar AS c1 WHERE c1.M = 12 AND c1.D = 26 AND c1.DW = 2 AND EXISTS (SELECT 1 FROM Calendar AS c2 WHERE c2.M = 12 AND c2.D = 25 AND c2.DW = 1 AND c2.[Date] < c1.[Date]);