Facility9

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