# 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]);```