The Value Of Calendar Tables, Part 1: Build A Table

Calendar tables (AKA date dimensions) are a fundamental component of any data warehouse, but these tables are also quite useful for OLTP systems. In this two-part series, we will look first at how to build a calendar table and then, in the second part of the series, we will see some reasons why you might want a calendar table even in your transactional processing systems.

Tip 1: Don’t Reinvent the Wheel

You can find oodles of posts out there about building calendar tables or date dimensions. One of my favorites comes from Aaron Bertrand, whose code I adopted and adapted for this post. You can easily find dozens of versions of these types of tables, so before you decide you need to build your own from first principles, gain some inspiration from what others have done before.

Tip 2: Don’t Sweat Load Time

Another thing to keep in mind here is that you’re only going to load your calendar table once, so if it takes two minutes to do, who really cares? The version I have should run reasonably fast–I calculated 726 years on slow hardware in 19 seconds and fast hardware in 11 seconds. I’m sure you can play code golf and get it done faster, but that’s probably not a good use of your time.

What you want to sweat instead is query time: how long is it taking to access this data?

The Script

First, let’s create a calendar table. Note that I added a primary key on the date column and a unique key on the DateKey column. In a warehouse, we would use DateKey everywhere; in a transactional system, I’d be more inclined to use the date itself.

CREATE TABLE dbo.Calendar
(
	DateKey INT NOT NULL,
	[Date] DATE NOT NULL,
	[Day] TINYINT NOT NULL,
	DayOfWeek TINYINT NOT NULL,
	DayName VARCHAR(10) NOT NULL,
	IsWeekend BIT NOT NULL,
	DayOfWeekInMonth TINYINT NOT NULL,
	CalendarDayOfYear SMALLINT NOT NULL,
	WeekOfMonth TINYINT NOT NULL,
	CalendarWeekOfYear TINYINT NOT NULL,
	CalendarMonth TINYINT NOT NULL,
	MonthName VARCHAR(10) NOT NULL,
	CalendarQuarter TINYINT NOT NULL,
	CalendarQuarterName CHAR(2) NOT NULL,
	CalendarYear INT NOT NULL,
	FirstDayOfMonth DATE NOT NULL,
	LastDayOfMonth DATE NOT NULL,
	FirstDayOfWeek DATE NOT NULL,
	LastDayOfWeek DATE NOT NULL,
	FirstDayOfQuarter DATE NOT NULL,
	LastDayOfQuarter DATE NOT NULL,
	CalendarFirstDayOfYear DATE NOT NULL,
	CalendarLastDayOfYear DATE NOT NULL,
	FirstDayOfNextMonth DATE NOT NULL,
	CalendarFirstDayOfNextYear DATE NOT NULL,
	FiscalDayOfYear SMALLINT NOT NULL,
	FiscalWeekOfYear TINYINT NOT NULL,
	FiscalMonth TINYINT NOT NULL,
	FiscalQuarter TINYINT NOT NULL,
	FiscalQuarterName CHAR(2) NOT NULL,
	FiscalYear INT NOT NULL,
	FiscalFirstDayOfYear DATE NOT NULL,
	FiscalLastDayOfYear DATE NOT NULL,
	FiscalFirstDayOfNextYear DATE NOT NULL,
	CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED([Date]),
	CONSTRAINT [UKC_Calendar] UNIQUE(DateKey)
);
GO

Now that we have the table, let’s load it up. First the script and then I’ll explain it in bits and pieces.

DECLARE
	@StartDate DATE = '18000101',
	@NumberOfYears INT = 726;

--Remove ambiguity with regional settings.
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;

DECLARE
	@EndDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L5)
INSERT INTO dbo.Calendar
(
	DateKey,
	[Date],
	[Day],
	[DayOfWeek],
	[DayName],
	IsWeekend,
	DayOfWeekInMonth,
	CalendarDayOfYear,
	WeekOfMonth,
	CalendarWeekOfYear,
	CalendarMonth,
	[MonthName],
	CalendarQuarter,
	CalendarQuarterName,
	CalendarYear,
	FirstDayOfMonth,
	LastDayOfMonth,
	FirstDayOfWeek,
	LastDayOfWeek,
	FirstDayOfQuarter,
	LastDayOfQuarter,
	CalendarFirstDayOfYear,
	CalendarLastDayOfYear,
	FirstDayOfNextMonth,
	CalendarFirstDayOfNextYear,
	FiscalDayOfYear,
	FiscalWeekOfYear,
	FiscalMonth,
	FiscalQuarter,
	FiscalQuarterName,
	FiscalYear,
	FiscalFirstDayOfYear,
	FiscalLastDayOfYear,
	FiscalFirstDayOfNextYear
)
SELECT
	CAST(D.DateKey AS INT) AS DateKey,
	D.[DATE] AS [Date],
	CAST(D.[day] AS TINYINT) AS [day],
	CAST(d.[dayofweek] AS TINYINT) AS [DayOfWeek],
	CAST(DATENAME(WEEKDAY, d.[Date]) AS VARCHAR(10)) AS [DayName],
	CAST(CASE WHEN [DayOfWeek] IN (1, 7) THEN 1 ELSE 0 END AS BIT) AS [IsWeekend],
	CAST(ROW_NUMBER() OVER (PARTITION BY d.FirstOfMonth, d.[DayOfWeek] ORDER BY d.[Date]) AS TINYINT) AS DayOfWeekInMonth,
	CAST(DATEPART(DAYOFYEAR, d.[Date]) AS SMALLINT) AS CalendarDayOfYear,
	CAST(DENSE_RANK() OVER (PARTITION BY d.[year], d.[month] ORDER BY d.[week]) AS TINYINT) AS WeekOfMonth,
	CAST(d.[week] AS TINYINT) AS CalendarWeekOfYear,
	CAST(d.[month] AS TINYINT) AS CalendarMonth,
	CAST(d.[monthname] AS VARCHAR(10)) AS [MonthName],
	CAST(d.[quarter] AS TINYINT) AS CalendarQuarter,
	CONCAT('Q', d.[quarter]) AS CalendarQuarterName,
	d.[year] AS CalendarYear,
	d.FirstOfMonth AS FirstDayOfMonth,
	MAX(d.[Date]) OVER (PARTITION BY d.[year], d.[month]) AS LastDayOfMonth,
	MIN(d.[Date]) OVER (PARTITION BY d.[year], d.[week]) AS FirstDayOfWeek,
	MAX(d.[Date]) OVER (PARTITION BY d.[year], d.[week]) AS LastDayOfWeek,
	MIN(d.[Date]) OVER (PARTITION BY d.[year], d.[quarter]) AS FirstDayOfQuarter,
	MAX(d.[Date]) OVER (PARTITION BY d.[year], d.[quarter]) AS LastDayOfQuarter,
	FirstOfYear AS CalendarFirstDayOfYear,
	MAX(d.[Date]) OVER (PARTITION BY d.[year]) AS CalendarLastDayOfYear,
	DATEADD(MONTH, 1, d.FirstOfMonth) AS FirstDayOfNextMonth,
	DATEADD(YEAR, 1, d.FirstOfYear) AS CalendarFirstDayOfNextYear,
	DATEDIFF(DAY, fy.FYStart, d.[Date]) + 1 AS FiscalDayOfYear,
	DATEDIFF(WEEK, fy.FYStart, d.[Date]) + 1 AS FiscalWeekOfYear,
	CASE
		WHEN d.[month] >= 7 THEN d.[month] - 6
		ELSE d.[month] + 6
	END AS FiscalMonth,
	CASE d.[quarter]
		WHEN 1 THEN 3
		WHEN 2 THEN 4
		WHEN 3 THEN 1
		WHEN 4 THEN 2
	END AS FiscalQuarter,
	CONCAT('Q', CASE d.[quarter]
		WHEN 1 THEN 3
		WHEN 2 THEN 4
		WHEN 3 THEN 1
		WHEN 4 THEN 2
	END) AS FiscalQuarterName,
	YEAR(fy.FYStart) AS FiscalYear,
	fy.FYStart AS FiscalFirstDayOfYear,
	MAX(d.[Date]) OVER (PARTITION BY fy.FYStart) AS FiscalLastDayOfYear,
	DATEADD(YEAR, 1, fy.FYStart) AS FiscalFirstDayOfNextYear
FROM Nums n
	CROSS APPLY
	(
		SELECT
			DATEADD(DAY, n - 1, @StartDate) AS [DATE]
	) d0
	CROSS APPLY
	(
		SELECT
			d0.[date],
			DATEPART(DAY, d0.[date]) AS [day],
			DATEPART(MONTH, d0.[date]) AS [month],
			CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, d0.[date]), 0)) AS FirstOfMonth,
			DATENAME(MONTH, d0.[date]) AS [MonthName],
			DATEPART(WEEK, d0.[date]) AS [week],
			DATEPART(WEEKDAY, d0.[date]) AS [DayOfWeek],
			DATEPART(QUARTER, d0.[date]) AS [quarter],
			DATEPART(YEAR, d0.[date]) AS [year],
			CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, d0.[date]), 0)) AS FirstOfYear,
			CONVERT(CHAR(8), d0.[date], 112) AS DateKey
	) d
	CROSS APPLY
	(
		SELECT
			--Fiscal year starts July 1.
			FYStart = DATEADD(MONTH, -6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH, 6, d.[date])), 0))
	) fy
	CROSS APPLY
	(
		SELECT
			FYyear = YEAR(fy.FYStart)
	) fyint
WHERE
	n.n <= DATEDIFF(DAY, @StartDate, @EndDate)
ORDER BY
	[date] OPTION (MAXDOP 1);
GO

Second Verse Like the First, but Slower

There’s a bit going on there, so let’s take this a piece at a time.

DECLARE
	@StartDate DATE = '18000101',
	@NumberOfYears INT = 726;

Our august corporation was founded on the first of January in 1800 and will survive in the year 2525. Because that song is a pack of lies I tell you.

Next, I pulled these settings from Aaron’s post. This way region differences won’t show up. You’re in the US now.

--Remove ambiguity with regional settings.
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;

The next bit has us build up a CTE-based tally table, a construct useful enough to keep copying. Or one of these days I’ll memorize it.

WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L5)

Now I’m going to do something completely different: start with the FROM clause. Let’s ignore the SELECT portion for a bit.

FROM Nums n
	CROSS APPLY
	(
		SELECT
			DATEADD(DAY, n - 1, @StartDate) AS [DATE]
	) d0
	CROSS APPLY
	(
		SELECT
			d0.[date],
			DATEPART(DAY, d0.[date]) AS [day],
			DATEPART(MONTH, d0.[date]) AS [month],
			CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, d0.[date]), 0)) AS FirstOfMonth,
			DATENAME(MONTH, d0.[date]) AS [MonthName],
			DATEPART(WEEK, d0.[date]) AS [week],
			DATEPART(WEEKDAY, d0.[date]) AS [DayOfWeek],
			DATEPART(QUARTER, d0.[date]) AS [quarter],
			DATEPART(YEAR, d0.[date]) AS [year],
			CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, d0.[date]), 0)) AS FirstOfYear,
			CONVERT(CHAR(8), d0.[date], 112) AS DateKey
	) d

In this first block, we convert the numbers into dates starting with our pre-defined start date. Aaron’s post had him inserting into a temp table; I decided to do everything in a single query instead and used CROSS APPLY to hide away some of these repeatedly-copied segments.

Then, we have a couple more calculations to get fiscal year. In this scenario, my fiscal year starts July 1st and runs through June 30th. If your company has a fiscal year which starts in October, change the 6 below to a 10. If your company has a fiscal year which starts in the middle of the month, the story is a bit more complicated but it’s still doable.

	CROSS APPLY
	(
		SELECT
			--Fiscal year starts July 1.
			FYStart = DATEADD(MONTH, -6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(MONTH, 6, d.[date])), 0))
	) fy
	CROSS APPLY
	(
		SELECT
			FYyear = YEAR(fy.FYStart)
	) fyint
WHERE
	n.n <= DATEDIFF(DAY, @StartDate, @EndDate)

Now we will take a look at the columns coming back. I’m going to break these into three segments: common, calendar year, and fiscal year. You don’t need a fiscal date and a calendar date—they’re both the same date. The calendar versus fiscal split only affects things which depend upon the starting point of the year, not within a week or month.

First up is common columns:

	CAST(D.DateKey AS INT) AS DateKey,
	D.[DATE] AS [Date],
	CAST(D.[day] AS TINYINT) AS [day],
	CAST(d.[dayofweek] AS TINYINT) AS [DayOfWeek],
	CAST(DATENAME(WEEKDAY, d.[Date]) AS VARCHAR(10)) AS [DayName],
	CAST(CASE WHEN [DayOfWeek] IN (1, 7) THEN 1 ELSE 0 END AS BIT) AS [IsWeekend],
	CAST(ROW_NUMBER() OVER (PARTITION BY d.FirstOfMonth, d.[DayOfWeek] ORDER BY d.[Date]) AS TINYINT) AS DayOfWeekInMonth,
	CAST(DENSE_RANK() OVER (PARTITION BY d.[year], d.[month] ORDER BY d.[week]) AS TINYINT) AS WeekOfMonth,
	CAST(d.[monthname] AS VARCHAR(10)) AS [MonthName],
	d.FirstOfMonth AS FirstDayOfMonth,
	MAX(d.[Date]) OVER (PARTITION BY d.[year], d.[month]) AS LastDayOfMonth,
	MIN(d.[Date]) OVER (PARTITION BY d.[year], d.[week]) AS FirstDayOfWeek,
	MAX(d.[Date]) OVER (PARTITION BY d.[year], d.[week]) AS LastDayOfWeek,
	MIN(d.[Date]) OVER (PARTITION BY d.[year], d.[quarter]) AS FirstDayOfQuarter,
	MAX(d.[Date]) OVER (PARTITION BY d.[year], d.[quarter]) AS LastDayOfQuarter,
	DATEADD(MONTH, 1, d.FirstOfMonth) AS FirstDayOfNextMonth,

Then calendar year-specific items. These are the things that will differ from calendar to fiscal year, such as what day number we’ve reached or what quarter we’re in.

	CAST(DATEPART(DAYOFYEAR, d.[Date]) AS SMALLINT) AS CalendarDayOfYear,
	CAST(d.[week] AS TINYINT) AS CalendarWeekOfYear,
	CAST(d.[month] AS TINYINT) AS CalendarMonth,
	CAST(d.[quarter] AS TINYINT) AS CalendarQuarter,
	CONCAT('Q', d.[quarter]) AS CalendarQuarterName,
	d.[year] AS CalendarYear,
	FirstOfYear AS CalendarFirstDayOfYear,
	MAX(d.[Date]) OVER (PARTITION BY d.[year]) AS CalendarLastDayOfYear,
	DATEADD(YEAR, 1, d.FirstOfYear) AS CalendarFirstDayOfNextYear,

Finally, we have fiscal year details whose columns match the calendar year details.

	DATEDIFF(DAY, fy.FYStart, d.[Date]) + 1 AS FiscalDayOfYear,
	DATEDIFF(WEEK, fy.FYStart, d.[Date]) + 1 AS FiscalWeekOfYear,
	CASE
		WHEN d.[month] >= 7 THEN d.[month] - 6
		ELSE d.[month] + 6
	END AS FiscalMonth,
	CASE d.[quarter]
		WHEN 1 THEN 3
		WHEN 2 THEN 4
		WHEN 3 THEN 1
		WHEN 4 THEN 2
	END AS FiscalQuarter,
	CONCAT('Q', CASE d.[quarter]
		WHEN 1 THEN 3
		WHEN 2 THEN 4
		WHEN 3 THEN 1
		WHEN 4 THEN 2
	END) AS FiscalQuarterName,
	YEAR(fy.FYStart) AS FiscalYear,
	fy.FYStart AS FiscalFirstDayOfYear,
	MAX(d.[Date]) OVER (PARTITION BY fy.FYStart) AS FiscalLastDayOfYear,
	DATEADD(YEAR, 1, fy.FYStart) AS FiscalFirstDayOfNextYear

One thing to note is that I got lazy with the fiscal month and quarter. I probably should have driven from my fiscal start of year date, but because I knew it started in Q3 of the prior calendar year, I could easily do a translation.

I Need More Columns!

If you have a need for more columns, the nice thing about calendar tables is that you can add them. And like I said above, don’t sweat load performance. If you load part of the table and then perform a series of updates off of it, everything’s still fine because you’re only running the scripts once. If you want holidays, Aaron’s post has some or you can check out Daniel Hutmacher’s post.

If you need other columns, just figure out the calculation and extend what you’ve got.

The Inevitable Cliffhanger

At this point, we have a calendar table. In the next post, I’ll show you a few neat tricks you can do with one. Stay tuned!

4 thoughts on “The Value Of Calendar Tables, Part 1: Build A Table

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s