One of my recurring headaches was to maintain a list of holidays and working days for businesses. I got tired of "gotta get the holidays loaded" and "why aren't next year's dates in the table?". My solution was to make the process dynamic. My only caveat is that this is US, Anglo, and Christian holiday centric. this is where I live, but the concepts are similar for anywhere. Here's how I solved the problem.
Define Holidays
Most holidays follow certain rules.
- The easy ones are those holidays that occur on a specific day of a month. New Years, Christmas and Independence Day (US) are examples of this. They occur on specific days and "they float". So, if the date falls on a Saturday, the holiday will "float" to the preceeding Friday. If it falls on a Sunday then it will "float" to the following Monday.
- There are holidays that occur on a specific day of the week and a specific week of they month.
- There are holidays that are eccumenical. For whatever reason, US securities markets are closed on Good Friday and this holiday follows a recurring but varied cycle. I'm sure there are others, but this is the one I've encountered.
Armed with that informaiton, I came up with a HolidayDefintion table that contains Country, a common name for the holiday, the month, week and day on which it occurs, if it floats, if it's a federal (government) holiday and if it's a business holiday (i.e. Good Friday is a business but not a federal holiday). Here's that code..
CREATE TABLE dbo.HolidayDefinition
(
CountryCd CHAR(2) NOT NULL
, Holiday VARCHAR(50) NOT NULL
, MonthNbr TINYINT NOT NULL
, WeekNbr TINYINT NOT NULL
, DayNbr TINYINT NOT NULL
, IsFloating BIT NOT NULL
CONSTRAINT DF_HolidayDefinition_IsFloating DEFAULT ( (0) )
, IsFederal BIT NOT NULL
CONSTRAINT DF_HolidayDefinition_IsFederal DEFAULT ( (1) )
, IsBusiness BIT NOT NULL
CONSTRAINT DF_HolidayDefinition_IsBusiness DEFAULT ( (0) )
, CONSTRAINT PK_HolidayDefinition PRIMARY KEY NONCLUSTERED
( CountryCd ASC, Holiday ASC )
);
GO
CREATE UNIQUE CLUSTERED INDEX UK_HolidayDefinition ON dbo.HolidayDefinition
(
MonthNbr ASC, WeekNbr ASC, DayNbr ASC, CountryCd ASC
);
GO
ALTER TABLE dbo.HolidayDefinition
WITH CHECK ADD CONSTRAINT CK_HolidayDefinition CHECK ((CASE WHEN WeekNbr>(0)
THEN CASE WHEN IsFloating=(1) OR DayNbr>(7)
THEN (0)
ELSE (1)
END
ELSE (1)
END=(1)));
GO
ALTER TABLE dbo.HolidayDefinition
CHECK CONSTRAINT CK_HolidayDefinition;
GO
ALTER TABLE dbo.HolidayDefinition
WITH CHECK ADD CONSTRAINT CK_HolidayDefinition_DayNbr CHECK ((DayNbr>=(1) AND DayNbr<=(31)));
GO
ALTER TABLE dbo.HolidayDefinition
CHECK CONSTRAINT CK_HolidayDefinition_DayNbr;
GO
ALTER TABLE dbo.HolidayDefinition
WITH CHECK ADD CONSTRAINT CK_HolidayDefinition_MonthNbr CHECK ((MonthNbr>=(1) AND MonthNbr<=(12)));
GO
ALTER TABLE dbo.HolidayDefinition
CHECK CONSTRAINT CK_HolidayDefinition_MonthNbr;
GO
ALTER TABLE dbo.HolidayDefinition
WITH CHECK ADD CONSTRAINT CK_HolidayDefinition_WeekNbr CHECK ((WeekNbr>=(0) AND WeekNbr<=(5)));
GO
ALTER TABLE dbo.HolidayDefinition
CHECK CONSTRAINT CK_HolidayDefinition_WeekNbr;
GO
A few rules are enforced by the check constraints, but some aren't. First let me describe the rules that are enforced with constraints:
- Either a holday occurs on a specifc day or a day of the week and week of the month. If it occurs on a specific day then the week number must equal 0, otherwise it will fall on weeks 1 - 4 or 5 for the last (i.e. Memorial Day falls on the last Monday of May whereas Thanksgiving occurs on the 4th Thursday)
- If WeekNbr = 0 then DayNbr can be any number < 32. (That's right, you can have a February or April 31st if you like - this is the not-checked constraint)
- If WeekNbr > 0 then DayNbr is the day number coresponding to the day of the week
- Only holidays falling on a specific day can "float"
Create Definitions
Next you'll need to load holidays into the table. You can look them up if you like, but here's the statement I use to merge in the new dates:
-- create cte containing the source data to be merged into the target table...
;
WITH cte_HolidayDefinition
AS (
SELECT CountryCd = 'US'
, Holiday = 'New Years Day'
, MonthNbr = '1'
, WeekNbr = '0'
, DayNbr = '1'
, IsFloating = '1'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Martin Luther King Day'
, MonthNbr = '1'
, WeekNbr = '3'
, DayNbr = '2'
, IsFloating = '0'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Presidents Day'
, MonthNbr = '2'
, WeekNbr = '3'
, DayNbr = '2'
, IsFloating = '0'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Easter Sunday'
, MonthNbr = '4'
, WeekNbr = '1'
, DayNbr = '1'
, IsFloating = '0'
, IsFederal = '0'
, IsBusiness = '0'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Good Friday'
, MonthNbr = '4'
, WeekNbr = '1'
, DayNbr = '5'
, IsFloating = '0'
, IsFederal = '0'
, IsBusiness = '0'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Memorial Day'
, MonthNbr = '5'
, WeekNbr = '5'
, DayNbr = '2'
, IsFloating = '0'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Independence Day'
, MonthNbr = '7'
, WeekNbr = '0'
, DayNbr = '4'
, IsFloating = '1'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Labor Day'
, MonthNbr = '9'
, WeekNbr = '1'
, DayNbr = '2'
, IsFloating = '0'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Columbus Day'
, MonthNbr = '10'
, WeekNbr = '2'
, DayNbr = '2'
, IsFloating = '0'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Veterans Day'
, MonthNbr = '11'
, WeekNbr = '0'
, DayNbr = '11'
, IsFloating = '0'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Thanksgiving Day'
, MonthNbr = '11'
, WeekNbr = '4'
, DayNbr = '5'
, IsFloating = '0'
, IsFederal = '1'
, IsBusiness = '1'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Black Friday'
, MonthNbr = '11'
, WeekNbr = '4'
, DayNbr = '6'
, IsFloating = '0'
, IsFederal = '0'
, IsBusiness = '0'
UNION ALL
SELECT CountryCd = 'US'
, Holiday = 'Christmas Day'
, MonthNbr = '12'
, WeekNbr = '0'
, DayNbr = '25'
, IsFloating = '1'
, IsFederal = '1'
, IsBusiness = '1'
)
-- using an EXCEPT statement generates the true diferences between the cte source and the target table you want to merge into...
MERGE dbo.HolidayDefinition tgt
USING (
SELECT CountryCd
, Holiday
, MonthNbr
, WeekNbr
, DayNbr
, IsFloating
, IsFederal
, IsBusiness
FROM cte_HolidayDefinition
EXCEPT -- return only true differences
SELECT CountryCd
, Holiday
, MonthNbr
, WeekNbr
, DayNbr
, IsFloating
, IsFederal
, IsBusiness
FROM dbo.HolidayDefinition
) src
ON tgt.CountryCd = src.CountryCd
AND tgt.MonthNbr = src.MonthNbr
AND tgt.WeekNbr = src.WeekNbr
AND tgt.DayNbr = src.DayNbr
WHEN MATCHED THEN
UPDATE SET CountryCd = src.CountryCd
, Holiday = src.Holiday
, MonthNbr = src.MonthNbr
, WeekNbr = src.WeekNbr
, DayNbr = src.DayNbr
, IsFloating = src.IsFloating
, IsFederal = src.IsFederal
, IsBusiness = src.IsBusiness
WHEN NOT MATCHED THEN
INSERT ( CountryCd
, Holiday
, MonthNbr
, WeekNbr
, DayNbr
, IsFloating
, IsFederal
, IsBusiness
)
VALUES ( src.CountryCd
, src.Holiday
, src.MonthNbr
, src.WeekNbr
, src.DayNbr
, ISNULL(src.IsFloating, 0)
, ISNULL(src.IsFederal, 1)
, ISNULL(src.IsBusiness, 0)
) ;
GO
Whew, that was a lot of fun.
Building a Holiday Calendar
Now that we have the holidays defined, we're ready to start using them. I use them with a simple inline table-valued function.
IF OBJECT_ID(N'dbo.fn_HolidyCalendar', N'IF') IS NULL
BEGIN
EXEC sys.sp_executesql N'create function dbo.fn_HolidyCalendar() returns table as return(select 0 as dummy)';
END;
GO
ALTER FUNCTION dbo.fn_HolidyCalendar
(
@CountryCd CHAR(2) = 'US'
, @Year SMALLINT
)
RETURNS TABLE
/*
————————————————————————————————————————————————————————————————————————————————————————————————————
© 2012-15 · NightOwl Development · All rights Reserved
————————————————————————————————————————————————————————————————————————————————————————————————————
Purpose : Builds a dynamic list of holiday dates.
Returns : Returns Holiday date and other information about Holidays for the secified country and year.
Notes :
History :
Date Developer Jira Nbr Modification
—————————— —————————————— —————————— ————————————————————————————————————————————————————————————
2011-12-14 P. Hunter 0 Object created.
————————————————————————————————————————————————————————————————————————————————————————————————————
*/
AS
RETURN
SELECT h.CountryCd
, h.Holiday
, HolidayOn = CONVERT(DATE, CASE h.WeekNbr
WHEN 0
THEN CASE h.IsFloating
WHEN 0 -- doesn't float
THEN r.HolidayOn
-- adjust floating holidays if they fall on Sunday or Saturday
ELSE DATEADD(DAY,
CASE DATENAME(WEEKDAY,
r.HolidayOn)
WHEN 'Sunday'
THEN 1
WHEN 'Saturday'
THEN -1
ELSE 0
END, r.HolidayOn)
END
ELSE r.HolidayOn
END)
, h.MonthNbr
, h.WeekNbr
, h.DayNbr
, h.IsFloating
, h.IsFederal
, h.IsBusiness
FROM dbo.HolidayDefinition h
CROSS APPLY (
-- convert the definition to beginning and end of the month dates
SELECT BOM = CONVERT(DATE, CONVERT(CHAR(4), @Year)
+ RIGHT(CONVERT(CHAR(5), ( 100 * h.MonthNbr ) + 10001), 4))
, EOM = CASE h.WeekNbr
WHEN 5
THEN DATEADD(DAY, -1,
DATEADD(MONTH, 1,
CONVERT(DATE, CONVERT(CHAR(4), @Year)
+ RIGHT(CONVERT(CHAR(5), ( 100
* h.MonthNbr )
+ 10001), 4))))
END
) m
CROSS
APPLY (
-- determine the raw offset days to apply to get to the holiday date
SELECT offset = CASE h.WeekNbr
WHEN 0 -- occurs on a specific day so, add this to BOM
THEN h.DayNbr - 1
WHEN 5 -- occurs on the last specified weekday of the month
THEN CASE WHEN DATEPART(WEEKDAY, m.EOM) >= h.DayNbr
THEN -( DATEPART(WEEKDAY, m.EOM) - h.DayNbr )
ELSE -( ( DATEPART(WEEKDAY, m.EOM) - h.DayNbr )
+ 7 )
END
ELSE CASE WHEN h.DayNbr >= DATEPART(WEEKDAY, m.BOM)
THEN ( h.DayNbr - DATEPART(WEEKDAY, m.BOM) )
+ ( h.WeekNbr - 1 ) * 7
ELSE ( h.DayNbr - DATEPART(WEEKDAY, m.BOM) )
+ ( h.WeekNbr * 7 ) -- days before/after the first day of the month
END
END
) o
CROSS
APPLY (
-- create a esitmated holiday date based on the offset and WeekNbr
-- this should be correct, except for floating holidays (WeekNbr = 0).
SELECT HolidayOn = DATEADD(DAY, o.offset, CASE h.WeekNbr
WHEN 5 THEN m.EOM
ELSE m.BOM
END)
) r
WHERE h.CountryCd = @CountryCd
AND ( h.IsFederal = 1
OR h.IsBusiness = 1
)
AND h.Holiday NOT IN ( 'Good Friday', 'Easter Sunday' )
UNION
ALL
-- retrieve good friday based on the ecumenical calcs for easter sunday
SELECT h.CountryCd
, h.Holiday
, r.HolidayOn
, r.MonthNbr
, WeekNbr = 0
, r.DayNbr
, h.IsFloating
, h.IsFederal
, h.IsBusiness
FROM (
SELECT Holiday = 'Good Friday'
, HolidayOn = CONVERT(DATE, CONVERT(CHAR(4), @Year)
+ CASE WHEN e.d > 31 THEN '04'
ELSE '03'
END + RIGHT(CONVERT(CHAR(3), 100
+ CASE WHEN e.d > 31 THEN e.d - 31
ELSE e.d
END - 2), 2))
, MonthNbr = CASE WHEN e.d > 31 THEN 4
ELSE 3
END
, DayNbr = CASE WHEN e.d > 31 THEN e.d - 31
ELSE e.d
END
FROM (
SELECT d.g
, d.c
, d.h
, d.i
, d.d
, IsFalse = CONVERT(BIT, 0)
FROM (
SELECT c.g
, c.c
, c.h
, c.i
, d = c.i - ( ( @Year + ( @Year / 4 ) + c.i
+ 2 - c.c + ( c.c / 4 ) )
% 7 ) + 28
FROM (
SELECT b.g
, b.c
, b.h
, i = ( b.c - ( b.c / 4 ) - ( ( 8
* b.c + 13 )
/ 25 ) + 19
* b.g + 15 ) % 30
FROM (
SELECT a.g
, a.c
, h = ( a.c - ( a.c / 4 )
- ( ( 8 * a.c + 13 )
/ 25 ) + 19
* a.g + 15 ) % 30
FROM (
SELECT g = @Year % 19
, c = @Year / 100
) a
) b
) c
) d
) e
) r
CROSS
APPLY (
SELECT *
FROM dbo.HolidayDefinition d
WHERE d.CountryCd = @CountryCd
AND d.Holiday = r.Holiday
AND ( d.IsFederal = 1
OR d.IsBusiness = 1
)
) h;
GO
I don't know how this code wil turn out, but the calcs to find Easter can be found in a lot of places. I used this source and SQLized it. Now all you have to do is get a number table, calculate the number of days and years between a start and end date, and you can spontaneously generate a custom calendar with holidays and business days. I'm attaching the files for this post. Holidays.sql has all of the code listed in the post, the Numbers function is the one I use anytime I need a number table and the Calendar function completes the generate of a contiguous calendar with business days and holidays. I hope they are as useful for you as they've been for me.
