Code.I.net: The Auxiliary Calendar Table For SQL Server 2005

A common auxiliary table is the calendar table. A calendar table is a table with a row for each and every date within a period. The table contains a column for the date and columns for additional data about the date, most useful in SQL Server would be ISO week as the datepart function is totally off.
But as we are at it we might as well split the date into year, month, day, quarter, add a holiday flag, a workday flag and a description column to indicate special days.
The calendar table is of course also useful in outer joins where you want to retrieve a row for every date even if there are no data for that date.
With the advent of CTE's creating a table just for the join is no longer necessary as you can use a recursive CTE for it, much as shown in my earlier post.
Continuing on the standard auixiliary schema:
Here comes the commented script for creating and populating the table:

CREATE TABLE [Auxiliary].[Calendar] (
-- This is the calendar table
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE [Auxiliary].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))

ADD CONSTRAINT [Calendar_ck] CHECK ( ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week] BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO

SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601

-- thus first day of a week is monday

WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),

DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),

Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

-- Fiscal year may be different to the actual year, in Norway the are the same

YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- I wrote about the computus function here

WHEN (DATEPART(DayOfYear, d.Date) = 1) -- New Year's Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))-7) -- Palm Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-3) -- Maundy Thursday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-2) -- Good Friday
OR (d.Date = Auxiliary.Computus(YEAR(Date))) -- Easter Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- Ascension Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- Pentecost
OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) -- Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) -- Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) -- Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) -- Boxing day
THEN 'HOLIDAY'
WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
WHEN (DATEPART(DayOfYear, d.Date) = 1) THEN 'New Year''s Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7) THEN 'Palm Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3) THEN 'Maundy Thursday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2) THEN 'Good Friday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))) THEN 'Easter Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN 'Ascension Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN 'Pentecost'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN 'Whitmonday'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) THEN 'Labour day'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) THEN 'Constitution day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) THEN 'Cristmas day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) THEN 'Boxing day'
END Description

FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
inner join Weeks w
on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)