# 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 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

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.

## 7 thoughts on “Building a calendar dimension with public holidays”

1. Boris Schubert

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 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
UNION ALL
— the rest of your code here after UNION ALL

• Daniel Hutmacher

Hey, thanks!

2. Boris Schubert

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

• Daniel Hutmacher

Gotcha. I’ve been meaning to update this post for quite a while now, so I’ll take that with me.

3. Jonas Hansson

Is it possible to get to get one script with the boths included scrips in one script to get the bugg fixed that Boris made??

• Daniel Hutmacher

Yeah, I’ll look into that!

This site uses Akismet to reduce spam. Learn how your comment data is processed.