Building a calendar dimension with public holidays

Whenever you’re building a data warehouse or similar solution, you’ll probably want to have a “calendar dimension”, a table that contains all days in a range of years. A challenge with this type of table is getting all the public holidays right, which could be particularly important if your business depends on this, like financial markets or logistics.

Here’s a “base” of code that you could build your solution on. I’ve used swedish public holidays as a start, but you’ll probably have to update the list to your country‘s standard. As for the easter and ascension day calculations, I’ve used the easter function that we looked at in last week’s post.

--- This is the year we're going to look at:
DECLARE @yearStart date={d '2014-01-01'};

--- Recursive CTE to loop through each day of the year.
WITH dates ([date], observance)
AS (
    SELECT @yearStart AS [date], CAST(NULL AS varchar(100)) AS observance
    UNION ALL
    SELECT d.[date], CAST((CASE
        WHEN fn.[month]=1 AND fn.[day]=1 THEN 'New year''s day'
        WHEN fn.[month]=1 AND fn.[day]=5 THEN 'Twelfth night (Trettondagsafton)'
        WHEN fn.[month]=1 AND fn.[day]=6 THEN 'Twelfth day (Trettondagen)'
        WHEN fn.[month]=6 AND fn.[day]=6 THEN 'National day'
        WHEN d.[date]=DATEADD(dd, -2, easter.[date]) THEN 'Good friday'
        WHEN d.[date]=DATEADD(dd, -1, easter.[date]) THEN 'Holy saturday (Påskafton)'
        WHEN d.[date]=easter.[date] THEN 'Easter'
        WHEN d.[date]=DATEADD(dd, 1, easter.[date]) THEN 'Day after easter (Annandag påsk)'
        WHEN d.[date]=DATEADD(dd, -3, DATEADD(ww, 6, easter.[date])) THEN 'Ascension day'
        WHEN fn.[month]=5 AND fn.[day]=1 THEN 'May day'
        --- Friday between june 19 and 25
        WHEN fn.[month]=6 AND fn.[day]>=19 AND fn.[day]<=25 AND fn.wkday=5 THEN 'Midsummer''s eve'
        --- Saturday between june 20 and 26
        WHEN fn.[month]=6 AND fn.[day]>=20 AND fn.[day]<=26 AND fn.wkday=6 THEN 'Midsummer''s day'
        --- Friday between october 30 and november 5
        WHEN (fn.[month]=10 AND fn.[day]>=30  OR
            fn.[month]=11 AND fn.[day]<=5) AND fn.wkday=5 THEN 'All saints'' eve (Allhelgonaafton)'
        --- Saturday between october 31 and november 6
        WHEN (fn.[month]=10 AND fn.[day]=31 OR
            fn.[month]=11 AND fn.[day]<=6) AND fn.wkday=6 THEN 'All saints'' day (Allhelgonadagen)'
        WHEN fn.[month]=12 AND fn.[day]=24 THEN 'Christmas eve'
        WHEN fn.[month]=12 AND fn.[day]=25 THEN 'Christmas day'
        WHEN fn.[month]=12 AND fn.[day]=26 THEN 'Boxing day'
        --- Regular saturdays and sundays
        WHEN fn.wkday=6 THEN 'Saturday'
        WHEN fn.wkday=7 THEN 'Sunday'
        END) AS varchar(100)) AS observance
    FROM dates
    CROSS APPLY (
        SELECT DATEADD(dd, 1, [date]) AS [date]
        ) AS d
    CROSS APPLY (
        SELECT DATEPART(dd, d.[date]) AS [day],
               DATEPART(mm, d.[date]) AS [month],
               --- DATEFIRST-neutral day of week; 1=monday..7=sunday:
               1+(DATEPART(dw, d.[date])+@@DATEFIRST-2)%7 AS [wkday]
        ) AS fn
    CROSS APPLY (
        SELECT dbo.fn_easterSunday(DATEPART(yy, @yearStart)) AS [date]
        ) AS easter
    WHERE d.[date]<DATEADD(yy, 1, @yearStart))

SELECT *
FROM dates
OPTION (MAXRECURSION 366);

Important: If you’re building this type of dimension or lookup table for historic years, keep in mind that public holidays vary over time. The swedish national day, for instance, wasn’t a public holiday until recent years.