This article is for relative newcomers to SQL. But, please keep reading. If you are reading this because of the words “Calendar Table” in the title, that means you probably don’t have one in place already. But if you work with dates in your data, calculating turnaround times or periodic totals, you should have one. Properly constructed calendar tables can turn complicated date calculations into simple, efficient queries. This article will show you how to create a sample Calendar table and then illustrate how easy queries become for otherwise odd questions.
The Tally Table
A Tally table (or numbers table) is a source of sequentially-numbered rows (usually 1 through 1,000,000 when implemented as a physical table.) It is a powerful tool for writing set-based code, instead of using procedural loops. One of the more well-known methods of generating these sequential numbers is attributed to Itzik Ben-Gan. For convenience sake, I created a view named vTally using this technique, and all the code examples will reference this view.
How the above code works is the subject for another article. For now, what it does is more important. This code can generate 100,000,000 rows in a few seconds or 100,000 rows almost instantly. The rows will have one column [N] , which will contain sequential values between 1 and 100 million. We will use these rows, in conjunction with a function called DATEADD(), to increment a starting date and produce a result set of thousands of sequential dates.
You can read more about tally tables in: The "Numbers" or "Tally" Table: What is is and how it replaces a loop.
The simplest form of Calendar table is a set of rows containing sequential dates. To create one, we simply pick a starting date and add a day to it over and over. The DATEADD() function is used to increment the starting date by a value of N-1 from vTally. DATEADD() works with all date/time datatypes and works with any increment of time from seconds up to years. It knows how many days are in each month and even handles adding a 29th day to February during leap years.
WITH Dates (N, CalendarDate) as (SELECT TOP(1000) N, DATEADD(DAY,N-1, CONVERT(DATE,'1/1/2016')) FROM vTally) SELECT N, CalendarDate FROM Dates;
The simple calendar is fairly limited, but still might be used for identifying dates when employees didn’t clock in for work. Below we’ll test a three-day period of time (2/15/2016 – 2/17/2016), so we only have to pull the TOP(3) rows from vTally
CREATE TABLE #EmployeeTime (TimeID int primary key identity(1,1), EmployeeID int, WorkDate date, InTime time, OutTime time) INSERT INTO #EmployeeTime VALUES (1,'2016-02-15','8:00','17:00') ,(2,'2016-02-15','8:00','17:00') ,(3,'2016-02-15','8:00','17:00') ,(1,'2016-02-16','8:00','17:00') ,(3,'2016-02-16','8:00','17:00') ,(2,'2016-02-17','8:00','17:00') ,(3,'2016-02-17','8:00','17:00') SELECT * from #EmployeeTime GO WITH Dates (N,CalendarDate) as (SELECT TOP(3) N, DATEADD(DAY,N-1, CONVERT(DATE,'2/15/2016')) FROM vTally) ,Employees as (SELECT DISTINCT EmployeeID from #EmployeeTime) SELECT e.EmployeeID, CalendarDate as MissedDate FROM Dates d CROSS APPLY Employees e LEFT JOIN #EmployeeTime t ON t.WorkDate = d.CalendarDate AND t.EmployeeID = e.EmployeeID WHERE t.WorkDate IS NULL order by EmployeeID, MissedDate
This produces the following results:
This query is somewhat useful, but it begs the question of whether or not the missing days fell on a weekend or a holiday. To perform more powerful and flexible queries, we need to expand the simple calendar and store it as a physical table.
Expanded Calendar Table
To get more use out of our calendar, we need to predefine more attributes in additional columns. We can use DATEPART() and other date functions to break out the various attributes of each CalendarDate.
CREATE TABLE #Holidays2016 (HolidayDate DATE, HolidayName VARCHAR(50)); INSERT INTO #Holidays2016-- partial list of American holidays for 2016 VALUES ('1/1/2016', 'New Years Day') , ('7/4/2016', 'Independence Day') , ('12/25/2016', 'Christmas Day'); WITH Dates (N, CalendarDate) as (SELECT TOP(366) N, DATEADD(DAY,N-1, CONVERT(DATE,'1/1/2016')) FROM vTally) SELECT N as DateID, CalendarDate ,DATEPART(day,CalendarDate) as CDay ,DATEPART(month,CalendarDate) as MonthNo ,DATEPART(year,CalendarDate) as YearNo ,DATEPART(DW,CalendarDate) as [DayofWeek] ,CASE WHEN EOMONTH(CalendarDate) = CalendarDate THEN 1 ELSE 0 END as EndOfMonth ,CASE WHEN DATEPART(DW,CalendarDate) IN (1,7) THEN 1 ELSE 0 END as Weekend ,CASE WHEN HolidayDate IS NOT NULL THEN 1 ELSE 0 END as Holiday ,CASE WHEN DATEPART(DW,CalendarDate) NOT IN (1,7) AND HolidayDate IS NULL THEN 1 ELSE 0 END as WorkDay FROM Dates d LEFT JOIN #Holidays2016 h ON d.CalendarDate = h.HolidayDate
DateID CalendarDate CDay MonthNo YearNo DayofWeek EndOfMonth Weekend Holiday WorkDay
1 2016-01-01 1 1 2016 6 0 0 1 0
2 2016-01-02 2 1 2016 7 0 1 0 0
3 2016-01-03 3 1 2016 1 0 1 0 0
4 2016-01-04 4 1 2016 2 0 0 0 1
5 2016-01-05 5 1 2016 3 0 0 0 1
6 2016-01-06 6 1 2016 4 0 0 0 1
7 2016-01-07 7 1 2016 5 0 0 0 1
8 2016-01-08 8 1 2016 6 0 0 0 1
9 2016-01-09 9 1 2016 7 0 1 0 0
10 2016-01-10 10 1 2016 1 0 1 0 0
Some of the most common date-oriented questions involve identifying the number of workdays between dates. The expanded calendar flags days as workdays, weekends, or holidays. On my system, each week begins with Sunday (day 1) and ends with Saturday (day 7), so the last CASE expression flags each day of the week (DW) as a weekend date when it’s day 1 or day 7.
A date is flagged as a holiday when a value for HolidayDate is returned from the LEFT JOIN to #Holidays2016 which contains a sample list of holidays. Holidays may fall on weekends and if so, both flags will be set. If a date is neither a holiday, nor a weekend day, it is a workday.
Before storing the expanded table, we use the CONVERT() function to produce smaller datatypes in order to minimize the disk space required. We’ll also add columns for more DATEPART() values, and a few unusual columns ([DoWAsc] and [LastDowInMonth) which will be explained in a moment.
CREATE TABLE #Holidays2016 (HolidayDate DATE, HolidayName VARCHAR(50)); INSERT INTO #Holidays2016 -- partial list of American holidays VALUES ('1/1/2016', 'New Years Day') , ('7/4/2016', 'Independence Day') , ('12/25/2016', 'Christmas Day'); GO WITH Dates (N, CalendarDate) as (SELECT TOP(75000) N, DATEADD(DAY,N-1,CONVERT(DATE,'1/1/1900')) FROM vTally) ,ExpandedCalendar as (SELECT N, CalendarDate ,CONVERT(smallint,DATEPART(day,CalendarDate)) as DayNo ,CONVERT(tinyint,DATEPART(week,CalendarDate)) as WeekNo ,CONVERT(tinyint,DATEPART(month,CalendarDate)) as MonthNo ,CONVERT(tinyint,DATEPART(quarter,CalendarDate)) as QuarterNo ,CONVERT(smallint,DATEPART(year,CalendarDate)) as YearNo ,CONVERT(tinyint,DATEPART(DW,CalendarDate)) as [DayofWeek] ,CONVERT(bit, CASE WHEN EOMONTH(CalendarDate) = CalendarDate THEN 1 ELSE 0 END) as EndOfMonth ,CONVERT(bit, CASE WHEN DATEPART(DW,CalendarDate) IN (1,7) THEN 1 ELSE 0 END) as Weekend ,CONVERT(bit, CASE WHEN HolidayDate IS NOT NULL THEN 1 ELSE 0 END) as Holiday ,CONVERT(bit, CASE WHEN DATEPART(DW,CalendarDate) NOT IN (1,7) AND HolidayDate IS NULL THEN 1 ELSE 0 END) as WorkDay FROM Dates d LEFT JOIN #Holidays2016 h ON d.CalendarDate = h.HolidayDate) SELECT *,CONVERT(bit, CASE WHEN ROW_NUMBER() OVER(PARTITION BY YearNo,MonthNo,[DayofWeek] ORDER BY N DESC) = 1 THEN 1 ELSE 0 END) as LastDowInMonth ,CONVERT(tinyint, ROW_NUMBER() OVER(PARTITION BY YearNo,MonthNo,[DayofWeek] ORDER BY N)) as DoWAsc ,CONVERT(tinyint,((DayNo-1)/7)+1) as WeekNoAlt INTO dbo.Calendar FROM ExpandedCalendar; GO CREATE UNIQUE CLUSTERED INDEX PK_Calendar on dbo.Calendar(CalendarDate); GO CREATE NONCLUSTERED INDEX X1_Calendar on dbo.Calendar(YearNo,MonthNo); GO SELECT TOP(10) * FROM dbo.Calendar WHERE CalendarDate >= '2016-1-1' ORDER BY CalendarDate
The results of the above query are too wide to display here, so we’re just going to have to discuss the uses of the columns. YearNo, QuarterNo, MonthNo, and WeekNo are obviously useful for GROUP BY columns in summary queries. [DayOfWeek] allows selection of just certain days (Tuesdays and Thursdays) when required. The Weekend, Holiday, and Workday flags help filter out unwanted days from consideration. Now for the seemingly odd columns.
The column [DoWAsc] is used to number days with the same day of week within a month. This is handy for identifying descriptive dates such as “the third Sunday in March.”
The [LastDowInMonth] flag identifies dates like “the last Tuesday in April.”
The [WeekNoAlt] column is an alternative to the [WeekNo] column generated by DATEPART(). It represents the first 7 days starting with January 1st, the second 7 days etc. This probably bears illustrating.
CalendarDate DayofWeek WeekNo DayNo WeekNoAlt
2016-01-01 6 1 1 1
2016-01-02 7 1 2 1
2016-01-03 1 2 3 1
2016-01-04 2 2 4 1
2016-01-05 3 2 5 1
2016-01-06 4 2 6 1
2016-01-07 5 2 7 1
2016-01-08 6 2 8 2
2016-01-09 7 2 9 2
2016-01-10 1 3 10 2
As you can see, the [WeekNo] column starts with a two-day period and increments each time [DayOfWeek] = 1. But [WeekNoAlt] counts 7 days starting with January 1st and increments every 8th day. The calculation ((DayNo-1)/7)+1) can be easily modified to count periods of 30 days (or 5 or 10 or 12 or whatever you need) by simply replacing the 7 with the number of your choice.
Other columns are possible, such as FiscalYear, FiscalQuarter, and FiscalMonth where an organization’s fiscal year does not follow the calendar year. Flags might be created for holidays of different nations or workdays of different nations. Don’t hesitate to add columns which are beneficial to you. The entire table can be recreated in seconds, and it will save you time and effort ever after.
Since I just mentioned holidays, I might as well address the fact that only three days in 2016 were flagged as Holidays. The next article will discuss creating a Holidays table that spans multiple years. Otherwise, dates have to be entered into a Holiday table manually.
USING the Enhanced Calendar Table
Now that you have a Calendar table in place, you can do basic calculations like finding the number of days between dates.
SELECT Count(*)-1 as Date_Diff FROM Calendar WHERE CalendarDate BETWEEN '2/27/2016' and '3/1/2016'
To be honest, there is already a DATEDIFF() function that can produce that same result. And there are long, nested, sometimes convoluted, strings of functions that answer more complicated date-related questions. But the enhanced calendar table can answer such questions easily.
For example, how many workdays fall between June 27 and July 19th, 2016? We can use a query like this:
SELECT SUM(1) as Workdays FROM dbo.Calendar WHERE CalendarDate BETWEEN '6/27/2016' and '7/19/2016' AND WorkDay = 1
How much disk space is all this costing me?
EXEC sp_spaceused Calendar
name Rows reserved Data index_size Unused
Calendar 75000 3472 KB 2376 KB 928 KB 168 KB
As the preceding examples illustrated, a properly constructed calendar table can simplify complicated calculations. Its columns can be easily expanded and the entire table regenerated in a matter of seconds. The flexibility it provides makes this table a must for any developer who has to work with date-related data.