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
    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
        SELECT DATEADD(dd, 1, [date]) AS [date]
        ) AS d
        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
        SELECT dbo.fn_easterSunday(DATEPART(yy, @yearStart)) AS [date]
        ) AS easter
    WHERE d.[date]<DATEADD(yy, 1, @yearStart))

FROM dates

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.

4 thoughts on “Building a calendar dimension with public holidays

  1. Hi, theres a minor bug in this CTE. It will not report YYYY-01-01 as New years day och you set @yearStart date={d ‘2014-01-01’}.
    As a simple fix, I added:
    SELECT @yearStart AS [date], CAST((CASE
    WHEN DATEPART(mm,@yearStart)=1 AND DATEPART(dd,@yearStart)=1 THEN ‘New years day’
    WHEN DATEPART(mm,@yearStart)=1 AND DATEPART(dd,@yearStart)=5 THEN ‘Twelfth night (Trettondagsafton)’
    WHEN DATEPART(mm,@yearStart)=1 AND DATEPART(dd,@yearStart)=6 THEN ‘Twelfth day (Trettondagen)’
    WHEN DATEPART(mm,@yearStart)=6 AND DATEPART(dd,@yearStart)=6 THEN ‘National day’
    WHEN @yearStart=DATEADD(dd, -2, dbo.fn_easterSunday(DATEPART(yy, @yearStart))) THEN ‘Good friday’
    WHEN @yearStart=DATEADD(dd, -1, dbo.fn_easterSunday(DATEPART(yy, @yearStart))) THEN ‘Holy saturday (Påskafton)’
    WHEN @yearStart=dbo.fn_easterSunday(DATEPART(yy, @yearStart)) THEN ‘Easter’
    WHEN @yearStart=DATEADD(dd, 1, dbo.fn_easterSunday(DATEPART(yy, @yearStart))) THEN ‘Day after easter (Annandag påsk)’
    WHEN @yearStart=DATEADD(dd, -3, DATEADD(ww, 6, dbo.fn_easterSunday(DATEPART(yy, @yearStart)))) THEN ‘Ascension day’
    WHEN datepart(mm,@yearStart)=5 AND DATEPART(dd,@yearStart)=1 THEN ‘May day’
    — Friday between june 19 and 25
    WHEN DATEPART(mm,@yearStart)=6 AND DATEPART(dd,@yearStart)>=19 AND DATEPART(dd,@yearStart)=20 AND DATEPART(dd,@yearStart)=30 OR
    DATEPART(mm,@yearStart)=11 AND DATEPART(dd,@yearStart)<=5) AND (1+(DATEPART(dw, @yearStart)+@@DATEFIRST-2)%7)=5 THEN 'All saints'' eve (Allhelgonaafton)'
    — Saturday between october 31 and november 6
    WHEN (DATEPART(mm,@yearStart)=10 AND DATEPART(dd,@yearStart)=31 OR
    DATEPART(mm,@yearStart)=11 AND DATEPART(dd,@yearStart)<=6) AND (1+(DATEPART(dw, @yearStart)+@@DATEFIRST-2)%7)=6 THEN 'All saints'' day (Allhelgonadagen)'
    WHEN DATEPART(mm,@yearStart)=12 AND DATEPART(dd,@yearStart)=24 THEN 'Christmas eve'
    WHEN DATEPART(mm,@yearStart)=12 AND DATEPART(dd,@yearStart)=25 THEN 'Christmas day'
    WHEN DATEPART(mm,@yearStart)=12 AND DATEPART(dd,@yearStart)=26 THEN 'Boxing day'
    — Regular saturdays and sundays
    WHEN (1+(DATEPART(dw, @yearStart)+@@DATEFIRST-2)%7)=6 THEN 'Saturday'
    WHEN (1+(DATEPART(dw, @yearStart)+@@DATEFIRST-2)%7)=7 THEN 'Sunday'
    –ELSE 'Profitday'
    END) AS VARCHAR(100)) AS observance
    — the rest of your code here after UNION ALL

  2. By the way.You forgott new year’s eve
    WHEN fn.[month]=12 AND fn.[day]=31 THEN ‘New year”s eve’

Let me hear your thoughts!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s