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.

Advertisements

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!

Metrics For Free: SQL Server Monitoring With Telegraf

Not too long ago, I had the opportunity to put into place a free solution for monitoring SQL Server instances. I saw Tracy’s series on collecting performance metrics InfluxDB + Telegraf + Grafana, and then I saw her talk on the topic (Collecting Performance Metrics), but until I implemented it myself, I couldn’t believe how easy it was. I thought it was going to take two or three days of hard work to get done, but I had everything going within a few hours.

Let’s walk through the process together.

Step 1: We Need a Database

The first piece of the puzzle is a place to host InfluxDB, an open source time series database.

Step 1A: We Need a Server

My customer is running most of their infrastructure in Azure, so I spun up a Linux-based host running Ubuntu. This doesn’t need to be a big server: I’m using a Standard D2s v3 (2 vCPUs, 8 GB of RAM) on a 100GB Premium SSD to host 5 servers, and my CPU never gets above 2%. This is the most expensive line item and will run about $70 or so a month in the East US region if you pay as you go and as little as $27 a month if you reserve for three years.

For a bigger environment, Tracy’s advice for 500 servers is 8 vCPUs and 32 GB of RAM.

Step 1B: We Need InfluxDB

Tracy shows how to install with CentOS but if you prefer Ubuntu (as I do), you’ll have to change a couple of things around. The installation instructions are pretty easy if you’ve worked with Linux before, so I won’t dally there.

Step 2: We Need Pretty Pictures

While you’re tooling around on that Linux VM, you should also install Grafana, a great tool for analytics and monitoring. Here are a couple examples of dashboards that you’ll get with this solution. First up is the instances at a glance dashboard:

Instances at a glance. 70% CPU? Just fine. Nothing to see here.

Next up is details on a specific instance, which let you look at activity, memory utilization, waits, CPU load, I/O load, etc.

A snippet of instance details.

Installing Grafana is easy. Tracy has scripts for CentOS or other Red Hat-based distributions and the Grafana website has step-by-step instructions for installing on Ubuntu or Debian-based distributions.

Step 3: We Need an Agent

InfluxDB is going to store all of your metrics and Grafana is going to display all of them, but we need one more piece of the puzzle: an agent to send metrics to InfluxDB at fixed intervals. That’s what Telegraf does. Grab the stable version of Telegraf or the Windows nightly if you’re feeling lucky. Unzip the archive and you’ll see two files: telegraf.conf and telegraf.exe. Here are the changes I made to my telegraf.conf file:

• In [[outputs.influxdb]] there is a section for URLs. Add your InfluxDB URL in a format like urls = ["http://10.10.10.10:8086"], replacing the IP address with your own. Your Telegraf agents need to be able to communicate with InfluxDB and by default, that’s over port 8086 but you can configure that port if you really need it to change.
• Still in [[outputs.influxdb]], I have uncommented the following lines: retention_policy = "", write_consistency = "any", and timeout = "5s".
• I added input plugins and shamelessly stole the code from Tracy. Here’s what that section looks like:

###############################################################################
#                            INPUT PLUGINS                                    #
###############################################################################
[[inputs.sqlserver]]
  servers = [
    "Server=localhost;Port=1433;User Id=telegraf;Password=<YOURPASSWORD>;app name=telegraf;log=1;",
  ]
  #
  ## Optional parameter, setting this to two will use a new version
  ## of the collection queries that break compatibility with the original
  ## dashboards. 
  query_version = 2
  ## If you are using AzureDB, setting this to True will gather resource utilization metrics
  # azuredb = False
  ## If you would like to exclude some of the metrics queries, list them here
  # exclude_query = [ 'PerformanceCounters','WaitStatsCatagorized' ]

[[inputs.win_perf_counters.object]]
  # Processor usage, alternative to native, reports on a per core.
  ObjectName = "Processor"
  Instances = ["*"]
  Counters = ["% Idle Time", "% Interrupt Time", "% Privileged Time", "% User Time", "% Processor Time"]
  Measurement = "sql_cpu"
  #IncludeTotal=false #Set to true to include _Total instance when querying for all (*).

[[inputs.win_perf_counters.object]]
  ObjectName = "LogicalDisk"
  Instances = ["*"]
  Counters = ["% Free Space","Free Megabytes","Avg. Disk Read Queue Length","Avg. Disk Write Queue Length","Avg. Disk sec/Read","Avg. Disk sec/Write"]
  Measurement = "sql_disk"

Note that you might need to change the servers entry above if you use named instances; that entry can also handle multiple instances if you have them installed on the same machine.

Once you make these configuration changes, you can deploy telegraf.exe and telegraf.conf to your Windows servers. Deploy them to %PROGRAMFILES%\telegraf\ and register Telegraf as a service using Powershell using the block below, which I copied from Tracy:

Stop-Service -Name telegraf -ErrorAction SilentlyContinue
& "c:\program files\telegraf\telegraf.exe" --service install -config "c:\program files\telegraf\telegraf.conf"
Start-Service -Name telegraf

If you want the full Powershell script which includes deploying remotely to multiple servers, you’re going to have to click the link and read her post.

Step 4: We Need to Hook Up Grafana

Grafana is installed on our Linux VM, but it’s not doing anything quite yet. In Grafana, mouse over the settings menu (the gear icon), select Data Sources, and click the “Add data source” button.

Adding a new data source to Grafana.

From there, you’ll see a menu where you get to enter values. The most important values are:

• Name — what you want to call this thing. I stuck with Tracy’s name because that way I could copy her dashboards with fewer changes on my part.
• Type — this needs to be InfluxDB.
• URL — because I’m hosting Grafana on the same server as InfluxDB, I’m going to localhost. If InfluxDB is on a different server, use that IP address instead.
• InfluxDB Details — we created a database on Influx called telegraf, so use that for the name and fill in your username and password.

Adding a new data source. There’s a save button below all this noise.

As far as security goes, Grafana seems to be fairly trusting of the outside world. In my case, I locked down the VM so that only people already on our VPN can access the server, and you can go one further by using nginx as a reverse proxy to control access and improve your security posture.

Step 5: We Need Cool-Looking Visuals

At this point, we have all of the pieces in place and data should be flowing into InfluxDB. We just need a way to display that data. You can build your own dashboards, but Tracy and Mark Wilkinson have a great set of starter dashboards for you.

To create a new dashboard, mouse over the dashboards icon and click “Manage.” Then click the “Import” button on the right.

MORE DASHBOARDS NOM NOM NOM

This will bring you to a page where you can import a dashboard in one of a few ways. I just copied and pasted the JSON straight from GitHub:

Adding a new dashboard.

Once you have the dashboard JSON imported, you should see the dashboard on the dashboards list.

Step 6: Invoice the Customer

At this point, you’re done. I thought it was going to take hours and hours to do, but it was really easy. The trickiest part was getting Azure security policies right and making sure all of my instances could see the server with Influx running on it. Otherwise, it was smooth sailing.

Coda: This Does Not Cover Everything

One thing to note is that this solution is a high-level monitoring solution. It does not include information on specific query plans or let you drill into currently-running queries. The purpose of this tool is to let you know that your server is in pain and give you some idea where the pain is: in other words, understand the current resource limitations and allow you to begin formulating conjectures about why this performance issue is happening. It does not do a great job of giving you the precise detail. From there, we have other tools for digging deeper once we know there is a problem.

If you want to stay on the free side, a few options for gathering more information include Query Store (or OpenQueryStore for older versions of SQL Server), running sp_whoisactive and capturing outputs, and collecting data from Extended Events. For more information on some of these techniques, check out Tracy’s GroupBy talk on collecting performance metrics.

Or you could shell out money and buy a product. That works too.

Using PHP With SQL Server

In our last technical post, I covered using Visual Studio Code to debug PHP code. Today, I’m going to cover integrating with SQL Server.

Step One: Get Drivers

Assuming that you followed the instructions for setting up PHP in the link above, you have a version of PHP running in C:\xampp\php. From there, you will want to download the Microsoft Drivers for PHP for SQL Server from Microsoft. The latest version of the drivers as of this post is 5.3, which covers PHP versions 7.0 through 7.2. This means that if you are using PHP 7.3, your version of PHP does not have official Microsoft support, so stick with 7.2 if this is a greenfield project.

Download the right version of the drivers given your operating system. For this post, I’ll assume that you’re running this on a Windows development box.

After you download the drivers, run the downloaded executable and extract to some temp directory. Then, copy the php_sqlsrv_72_ts_x86.dll file to C:\xampp\php\ext\ (assuming you installed Xampp to that directory and are running PHP 7.2) and modify your php.ini file to add the following at the bottom:

extension=php_sqlsrv_72_ts_x86.dll

Once you do that, restart Apache and you should be able to access SQL Server.

Step Two: Write Code

Querying SQL Server in PHP is pretty easy. Here is some overly simplistic code to execute a stored procedure. I’m going to give you bad PHP code (seriously, who takes PHP development advice from a database guy hasn’t really written PHP in a decade?) but at least I’m not going to make it SQL injectable…

private function executeQuery($query, $returnResult){
	$dbconnection = null;
		try {
			$serverName = "MYSERVER";
			$databaseName = "MYDATABASE";
			$username = "MYUSERNAME";
			$password = "MYPASSWORD";
			$connectionOptions = array("Database" => $databaseName, "UID" => $username, "PWD" => $password);
			$dbconnection = sqlsrv_connect($serverName, $connectionOptions);

			if($dbconnection){
				$preparedStatement = sqlsrv_prepare($dbconnection, $query['query'], $query['parameters']);
				$isSuccessful = sqlsrv_execute($preparedStatement);
				
				if($returnResult &&  isset($preparedStatement) && $isSuccessful){
					$finalResults = array();
					while($row = sqlsrv_fetch_array($preparedStatement, SQLSRV_FETCH_ASSOC))  
					{  
						$finalResults[] = $row;
					}  
					return $finalResults;
				}

				return null;
			} else {
				throw new Exception('Unable to connect to database');
			}
		} catch(Exception $error){
			throw new Exception($error);
		}
		$dbconnection = null;
}

In the code above, we take in a query, which has the query text and a set of parameters. We connect to SQL Server given various parameters, which you’d want to store in a secure location so please don’t copy my code verbatim; do it the right way.

If we want a result set back, set $returnResult to true. Otherwise, we don’t expect a result set back.

Given that structure, here’s how you’d call it.

private function createInsertActionLogDBQuery($logLevelID, $stackTrace, $errorMessage){
	return array("query" => "EXECUTE dbo.ActionLog_InsertActionLog
								@LogLevelID = ?,
								@StackTrace = ?,
								@ErrorMessage = ?;",
				"parameters" => array(
					array($logLevelID, SQLSRV_PARAM_IN),
					array($stackTrace, SQLSRV_PARAM_IN),
					array($errorMessage, SQLSRV_PARAM_IN)
				));
}

public function sendActionLog($logLevelID, $stackTrace, $errorMessage){
	$this->executeQuery($this->createInsertActionLogDBQuery($logLevelID, $stackTrace, $errorMessage), false);
}

We have one function call to build up our query object and another function which acts as the intermediary. That way, if we need to translate data from our app to SQL Server, we can do it in the public function without exposing that we’re sending data to SQL Server or the mechanisms behind the insert operation.

Step Three: Disclaim Knowledge

It was nice to take a jaunt back into a language I hadn’t seen in a very long time. I am by no means a fan of PHP and do not want to develop in it, but it was worthwhile spending some time to understand how things work in a non-.NET space. As I mentioned, I am absolutely not an expert in PHP, so you don’t want to take my advice on it seriously, but this process did end up working out, so I’m pretty happy about it.

Using Visual Studio Code For PHP Development

For a consulting engagement, I needed to be able to run a debugger against a PHP codebase as part of a migration project. Because my last experience with PHP was sometime last decade, I had no clue how to do this. Now I have a slight clue, so I figured I’d share that slight clue. Note that this is for installing PHP on Windows; the Linux experience is a bit more refined.

Step One: Install xampp

Grab the latest version of xampp at https://www.apachefriends.org/download.html.  As of the time of writing, the latest PHP version is 7.3, but grab the version for PHP 7.2 because the debugger DLL we will use does not currently support PHP 7.3.

Run the xampp installer and install in C:\xampp or a similar directory.  The only services we need are Apache httpd and PHP; you do not need to install MySQL, Tomcat, or any of the other services or administration tools.

Download xdebug

Xdebug is a debugger DLL for PHP, which you can download from http://xdebug.org.  We want to get the latest 32-bit, thread-safe, stable version which supports PHP 7.2 and is built using the Visual C++ 2017 redistributable.  As of 2018-12-28, that is http://xdebug.org/files/php_xdebug-2.6.1-7.2-vc15.dll.

The DLL we want.

After you have downloaded the appropriate DLL, copy it into C:\xampp\php\ext\ (assuming you installed to C:\xampp).  Then, open C:\xampp\php\php.ini and add the following lines to the bottom of the file:

[XDebug]
zend_extension = "C:\xampp\php\ext\php_xdebug-2.7.0beta1-7.3-vc15-nts-x86_64.dll"
xdebug.remote_enable = 1
xdebug.remote_autostart = 1

Configure Visual Studio Code

Visual Studio Code has built-in PHP support, but there are a couple of plugins which I like, specifically, PHP Intelephense and PHP Debug.

The two extensions I used for PHP debugging.

You can install these extensions by opening up Visual Studio, clicking the Extensions option (highlighted in the image above), and typing the name of the extension into the search box.  Click on the “Install” button next to each extension and when all extensions are installed, close and re-open Visual Studio Code.

Then, press the F1 key and type “User” into the console. This will give you a few options:

Computer: read my mind.

From that list, you want to select “Preferences: Open User Settings.” That will pop up the following window:

A whole bundle of things you can change to break your Visual Studio Code installation.

There are a number of settings you might want to configure here, but for now, type “PHP” into the search settings box above.  We need to validate the PHP executable path, so click the “Edit in settings.json” link:

PHP-specific user settings.

Clicking on the settings.json link will bring you to a side-by-side editor.  The left-hand side is immutable, but you can override its values on the right-hand side.  Set php.validate.executablePath to your installed PHP executable, making sure to double up any backslashes. In my case, it is:

"php.validate.executablePath": "C:\\xampp\\php\\php.exe"

Setting an external path.

Configure httpd

Navigate to C:\xampp\apache\conf (assuming you installed Xampp in C:\xampp) and open the httpd.conf file in a text editor.  In the “DocumentRoot” section, you will want to replace whatever is there with the root directory for your PHP code.

After you do that, open up the Xampp control panel and start the Apache service:

The Xampp control panel running Apache + PHP.

Configure VS Code For Debugging

In Visual Studio code, go to the Debug tab and ensure that you have two Debug configuration options: “Listen for XDebug” and “Launch currently open script.”

Two debugging configurations should be available.

If you do not have these two, click on the “Add Config (workspace)” and add the following JSON:

{
    // Use IntelliSense to learn about possible attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "name": "Listen for XDebug",
            "type": "php",
            "request": "launch",
            "port": 9000
        },
        {
            "name": "Launch currently open script",
            "type": "php",
            "request": "launch",
            "program": "${file}",
            "cwd": "${fileDirname}",
            "port": 9000
        }
    ]
}

Then, select Listen for XDebug and click the Play button.  This will create a hook between Visual Studio Code and your debugger over port 9000.  You might need to create a firewall rule to allow inbound traffic on port 9000.

From here, you can put add and remove breakpoints and by navigating to http://127.0.0.1/{mypage.php}, you can trigger the debugger because we have the XDebug DLL installed.

The Visual Studio Code documentation for debugging includes detail on how to navigate the debugger.

I’m Writing A Book

Due to having far too much free time (I mean, there are hours a day where I’m doing nothing but sleeping! More than one!), I’ve taken to the idea of writing a book on PolyBase. The working title is PolyBase Revealed and has a scheduled release date of “That’s way in the future so I don’t have to worry…wait, what month is it now? Oh crap!”

Long-time readers may recall my series on PolyBase (I still prefer “Polybase” and so does about a quarter of the Microsoft documentation, but I’ll capitalize the middle B for marketing purposes) focusing on SQL Server 2016. Well, SQL Server 2019 is coming and with it, we have a lot of new PolyBase-related functionality, including external connections to a slew of new resources. The book will focus on SQL Server 2019 with a chapter on Azure SQL Data Warehouse. My goal is to make it easy for someone getting started with data virtualization and with a T-SQL background to integrate with the different supported systems, pointing out bugaboos along the way. Because there are plenty of bugaboos.

As I write the book, I intend on adding to this blog the odds and ends that I find during research but which don’t make their way into the book. I’ll probably spam my blog with way too much info about the book because I expect to build a house made out of gold using my royalties. A house made out of gold is utterly impractical, but how else could I spend that much in royalty checks?

Southern Fried F# Conference In April

I’m part of the committee putting together Southern Fried F#, a conference which will take place in Raleigh, North Carolina on Saturday, April 13th.

No conference is complete without a great logo.

The Call for Papers is currently open and will remain open through February 15th, so if you are interested in presenting, get a move on—that way people won’t need to see me give a talk…

The idea of Southern Fried F# is to provide a full-day community conference dedicated to the F# programming language. We are aiming at introductory-level talks to build up interest in the language and show areas in which F# can thrive, whether that be using type providers to integrate external sources quickly, building business rules which are impossible to break and easy to understand, or building production-quality machine learning models.

We will host the 2019 edition of Southern Fried F# at Red Hat’s office in downtown Raleigh. More details will be available on the main website as we get closer to the event. You can also sign up for the event now—tickets are free.