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 to calculate 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]);
