The Value of Calendar Tables, Part 2: Simplify Queries

In the last post, I showed how to create a calendar table. In this post, we’ll look at a few places where calendar tables can simplify your queries significantly.

Finding Valid Dates

First up, let’s do a little bit of SQL Saturday Raleigh prep (notice how I slid that advertisement in so subtly?). We normally want to host our event sometime in April, so let’s look for valid dates over the next three years, as well as 2019 itself. The query itself is trivial:

SELECT
	c.Date
FROM dbo.Calendar c
WHERE
	c.MonthName = 'April'
	AND c.DayName = 'Saturday'
	AND c.CalendarYear BETWEEN 2019 AND 2022
ORDER BY
	c.Date;

That feeling when the guy in charge of the event says “No, I didn’t say pick one Saturday in April; I said pick every Saturday in April!”

Doing this in T-SQL without the assistance of a calendar table isn’t too awful, but it’s not as easy. With a tally table, you could build out enough rows and perform date calculations and filters…but it’s going to take you more than 9 lines of code.

Those Jealous Bunnies

There’s one problem with picking a SQL Saturday in April: Easter and Passover tend to run right around that time, and nobody wants a SQL Saturday on Passover or the day before Easter. Unfortunately, our calendar table doesn’t include holiday information. So let’s add it!

ALTER TABLE dbo.Calendar ADD HolidayName VARCHAR(40) NULL;

Now we have a column for holiday…but how do we get Easter and Passover info? Daniel Hutmacher has our back for Easter. Here’s my set-based version of his function:

CREATE TABLE #EasterBuilder
(
	Year INT,
	a TINYINT,
	b TINYINT,
	c TINYINT,
	d TINYINT,
	e TINYINT,
	f TINYINT,
	g TINYINT,
	h TINYINT,
	i TINYINT,
	k TINYINT,
	l TINYINT,
	M TINYINT,
	EasterDay DATE
);

INSERT INTO #EasterBuilder 
(
	Year,
	a,
	b,
	c
)
SELECT DISTINCT
	c.CalendarYear,
	c.CalendarYear % 19,
	FLOOR(1.0 * c.CalendarYear / 100),
	c.CalendarYear % 100
FROM dbo.Calendar c;

UPDATE #EasterBuilder
SET
	d = FLOOR(1.0 * b / 4),
	e = b % 4,
	f = FLOOR((8.0 + b) / 25);

UPDATE #EasterBuilder
SET
	g = FLOOR((1.0 + b - f) / 3);

UPDATE #EasterBuilder
SET
	h = (19 * a + b - d - g + 15) % 30,
	i = FLOOR(1.0 * c / 4),
	k = year % 4;

UPDATE #EasterBuilder
SET
	l = (32.0 + 2 * e + 2 * i - h - k) % 7;

UPDATE #EasterBuilder
SET
	m = FLOOR((1.0 * a + 11 * h + 22 * l) / 451);

UPDATE #EasterBuilder
SET
	EasterDay =
	DATEADD(dd, (h + l - 7 * m + 114) % 31,
	DATEADD(mm, FLOOR((1.0 * h + l - 7 * m + 114) / 31) - 1,
	DATEADD(yy, year - 2000, { D '2000-01-01' })
	)
	);

UPDATE c
SET
	HolidayName = 'Easter'
FROM dbo.Calendar c
	INNER JOIN #EasterBuilder eb
		ON c.Date = eb.EasterDay;

For Passover and a bunch of other holidays, I found a great set of functions by Karl Schmitt. This time, instead of converting functions to set-based operations, I’m going to use the functions as-is. By the way, if you want an easy script to insert into HolidayDef, here you go:

INSERT INTO dbo.HolidayDef 
(
	HolidayKey,
	OffsetKey,
	Type,
	FixedMonth,
	FixedDay,
	DayOfWeek,
	WeekOfMonth,
	Adjustment,
	HolidayName
)
VALUES
( 1, 0,'F', 1, 1,0,0,  0,'New Year''s Day'),
( 2, 0,'M', 1, 0,2,3,  0,'Martin Luther King Jr''s BD (Observed)'),
( 3, 0,'F', 2, 2,0,0,  0,'Ground Hog Day'),
( 4, 0,'F', 2,12,0,0,  0,'Lincoln''s Birthday'),
( 5, 0,'F', 2,14,0,0,  0,'Valentine''s Day'),
( 6, 0,'M', 2, 0,2,3,  0,'President''s Day'),
( 7,18,'O', 0, 0,0,0,-47,'Paczki Day (Mardi Gras)'),
( 8,18,'O', 0, 0,0,0,-46,'Ash Wednesday'),
( 9, 0,'F', 2,22,0,0,  0,'Washington''s Birthday'),
(10,15,'O', 0, 0,0,0,-30,'Purim'),
(11, 0,'F', 3,17,0,0,  0,'St. Patrick''s Day'),
(12, 0,'F', 3,19,0,0,  0,'St. Joseph''s Day'),
(13,18,'O', 0, 0,0,0,-14,'Passion Sunday'),
(14,18,'O', 0, 0,0,0, -7,'Palm Sunday'),
(15, 0,'S', 0, 0,0,0,  0,'Passover'),
(16,18,'O', 0, 0,0,0, -2,'Good Friday'),
(17, 0,'M', 4, 0,1,1,  0,'Daylight Savings Begins'),
(18, 0,'S', 0, 0,0,0,  0,'Easter Sunday'),
(19, 0,'S', 0, 0,0,0,  0,'Orthodox Easter'),
(20, 0,'M', 5, 0,7,1, -10,'Administrative Professionals Day'),
(21, 0,'F', 4,22,0,0,  0,'Earth Day'),
(22, 0,'M', 5, 0,1,2,  0,'Mother''s Day'),
(23, 0,'M', 5, 0,7,3,  0,'Armed Forces Day'),
(24, 0,'F', 5,31,0,0,  0,'Memorial Day'),
(25, 0,'F', 6,14,0,0,  0,'Flag Day'),
(26, 0,'M', 6, 0,1,3,  0,'Father''s Day'),
(27,18,'O', 0, 0,0,0, 49,'Pentecost'),
(28, 0,'F', 7, 4,0,0,  0,'Independence Day'),
(29, 0,'M', 9, 0,2,1,  0,'Labor Day'),
(30,15,'O', 0, 0,0,0,163,'Rosh Hashanah'),
(31, 0,'M', 9, 0,1,2,  0,'Grandparents Day'),
(32,15,'O', 0, 0,0,0,172,'Yom Kippur'),
(33,18,'O', 0, 0,0,0, 39,'Ascension Day'),
(34, 0,'F',10, 9,0,0,  0,'Leif Erikson Day'),
(35, 0,'M',10, 0,1,2,  0,'National Children''s Day'),
(36, 0,'M',10, 0,3,2,  0,'Columbus Day (Traditional)'),
(37, 0,'F',10,16,0,0,  0,'Boss''s Day'),
(38, 0,'M',10, 0,7,3,  0,'Sweetest Day'),
(39, 0,'M',11, 0,1,1, -7,'Daylight Savings Ends'),
(40, 0,'F',10,31,0,0,  0,'Halloween'),
(41, 0,'F',11, 1,0,0,  0,'All Saint''s Day'),
(42, 0,'M',11, 0,2,1,  1,'Election Day'),
(43, 0,'F',11,11,0,0,  0,'Veterans Day'),
(44, 0,'M',11, 0,5,4,  0,'Thanksgiving Day'),
(45, 0,'S', 0, 0,0,0,  0,'Chanukah'),
(46,18,'O', 0, 0,0,0, 56,'Trinity Sunday'),
(47, 0,'F',12,25,0,0,  0,'Christmas Day'),
(48,15,'O', 0, 0,0,0,177,'Sukkot'),
(49,15,'O', 0, 0,0,0,184,'Shemini Atzeret'),
(50,15,'O', 0, 0,0,0,185,'Simhat Torah (outside Isreal)'),
(51, 0,'F', 3,19,0,0,  0,'St. Josephs Day'),
(52,15,'O', 0, 0,0,0, 33,'Lag B''Omar'),
(53,15,'O', 0, 0,0,0, 50,'Shavuot'),
(54, 0,'S', 0, 0,0,0,  0,'Tu Bishvat'),
(55, 0,'S', 0, 0,0,0,  0,'Yom HaAtzma''ut'),
(56, 0,'S', 0, 0,0,0,  0,'Tisha B''Av');

I can then call dbo.GetHolidayDates for the time frame 1800-01-01 until 2525-12-31 and wait a little while because it’s a bunch of nested functions. Here’s how we get them into our holiday table:

CREATE TABLE #HolidayDays
(
	HolidayKey INT,
	HolidayDate DATE
);

INSERT INTO #HolidayDays 
(
	HolidayKey,
	HolidayDate
)
SELECT
	HolidayKey,
	HolidayDate
FROM dbo.GetHolidayDates(0, '1800-01-01', '2525-12-31');

UPDATE c
SET
	HolidayName = h.HolidayName
FROM dbo.Calendar c
	INNER JOIN #HolidayDays hd
		ON c.Date = hd.HolidayDate
	INNER JOIN dbo.HolidayDef h
		ON hd.HolidayKey = h.HolidayKey;

Now that I have this, I can make sure that we don’t schedule our SQL Saturday on the first day of Passover or on the day before Easter Sunday with the following:

USE Scratch
GO
WITH aprils AS
(
	SELECT
		c.Date,
		c.DayName,
		c.CalendarYear,
		c.HolidayName,
		LEAD(c.HolidayName, 1) OVER (ORDER BY c.Date) AS NextDayHoliday
	FROM dbo.Calendar c
	WHERE
		c.MonthName = 'April'
		AND c.CalendarYear BETWEEN 2019 AND 2022
)
SELECT
	a.Date,
	a.CalendarYear,
	a.NextDayHoliday
FROM aprils a
WHERE
	a.DayName = 'Saturday'
	AND ISNULL(a.HolidayName, '') <> 'Passover'
	AND ISNULL(a.NextDayHoliday, '') <> 'Easter Sunday'
ORDER BY
	a.Date;

This query got a little more complicated because of the way the LEAD window function works. LEAD runs after the WHERE clause, so I need to include at least Saturdays and Sundays. I decided to include all days in April just to make sure nothing wacky happens.

This gives me the following results:

Finding the right days. Like April 27th.

Fiscal Weeks of Year

Let’s say that I need to schedule a meeting for fiscal week 33 of the 2020 fiscal year. Finding this for calendar year isn’t too bad, but because companies have different fiscal years, you’ll have some trouble finding that online very easily. But we already took care of that in the past post, so this one’s easy again:

SELECT
	c.Date
FROM dbo.Calendar c
WHERE
	c.FiscalWeekOfYear = 33
	AND c.FiscalYear = 2020
ORDER BY
	c.Date ASC;

The next days I have open on my calendar.

Conclusion

In this post, we took a look at two ways that having a calendar table can make calculations significantly easier. As you have more complicated date queries, you’ll find that calendar tables can help out significantly and get you results quickly because those expensive calculations (like figuring out what day Easter Sunday lands on this year) only have to run once.