Share via

SQL Developers Toolbox – Calendar Tables explained

  • Article

Introduction

The concept of a calendar table is fairly straightforward. Like its friend the tally (or numbers) table, it’s not really part of your data model, but an add-on that can make your life easier.  Used properly, it can make complicated requests into simple queries. A good number of posts to the MSDN T-SQL forum are requests on how to handle requests such as “How many days are between two dates?” – A fairly simple question, which is quickly complicated by asking what types of date count. Holidays? Weekends? Other non-business days?

Demo Data

Let’s setup an example using some random data. We’ll build a short schema for a sales database.

DECLARE @invoices TABLE (invoiceID INT, invoiceSeqID INT, orderDate DATE, customerID INT, repID INT, itemID INT, qty INT)

DECLARE @items TABLE (itemID INT, itemName VARCHAR(20), price FLOAT)

DECLARE @reps TABLE (repID INT, repFirstName VARCHAR(20), repLastName VARCHAR(20))

DECLARE @customers TABLE (customerID INT, customerFirstName VARCHAR(20), customerLastName VARCHAR(20), customerCompanyName VARCHAR(20))

 

DECLARE @i INT = 0, @x INT = 0

DECLARE @rndCustomerID INT = ROUND(((100 - 1 -1) * RAND() + 1), 0) - 1    

DECLARE @rndRepID INT = ROUND(((20 - 1 -1) * RAND() + 1), 0) - 1                        

DECLARE @rndItemID INT = ROUND(((50 - 1 -1) * RAND() + 1), 0) - 1                       

DECLARE @rndQty INT = ROUND(((5 - 1 -1) * RAND() + 1), 0)                               

DECLARE @rndOrderDate DATE = DATEADD(DAY, -ROUND(((365 - 0 -1) * RAND() + 0), 0), CURRENT_TIMESTAMP)

DECLARE @rndInvoiceSeq INT = 0

DECLARE @rndFirstNameStr VARCHAR(20) = '', @rndLastNameStr VARCHAR(20) = '', @rndCompanyNameStr VARCHAR(20) = ''

DECLARE @rndPrice FLOAT = 0.0

 

SET NOCOUNT ON

 

WHILE @i < 50

BEGIN

 SET @x = ROUND(((15 - 10 -1) * RAND() + 10), 0)

 SET @rndFirstNameStr = ''

  WHILE @x < 20

  BEGIN

   IF LEN(@rndFirstNameStr) = 0 SET @rndFirstNameStr = @rndFirstNameStr + CHAR(ROUND(((91 - 65 -1) * RAND() + 65), 0)) ELSE SET @rndFirstNameStr = @rndFirstNameStr + CHAR(ROUND(((122 - 97 -1) * RAND() + 97), 0))

   SET @x = @x+1

  END

 SET @rndPrice = ((15 - 0.5 -1) * RAND() + 0.5)

 INSERT INTO @items (itemID, itemName, price) VALUES

 (@i, @rndFirstNameStr, @rndPrice)

 SET @i += 1

END

 

SET @i =  0

 

WHILE @i < 100

BEGIN

 SET @x = ROUND(((15 - 10 -1) * RAND() + 10), 0)

 SET @rndFirstNameStr = ''

  WHILE @x < 20

  BEGIN

   IF LEN(@rndFirstNameStr) = 0 SET @rndFirstNameStr = @rndFirstNameStr + CHAR(ROUND(((91 - 65 -1) * RAND() + 65), 0)) ELSE SET @rndFirstNameStr = @rndFirstNameStr + CHAR(ROUND(((122 - 97 -1) * RAND() + 97), 0))

   SET @x = @x+1

  END

 SET @x = ROUND(((15 - 10 -1) * RAND() + 10), 0)

 SET @rndLastNameStr = ''

  WHILE @x < 20

  BEGIN

   IF LEN(@rndLastNameStr) = 0 SET @rndLastNameStr = @rndLastNameStr + CHAR(ROUND(((91 - 65 -1) * RAND() + 65), 0)) ELSE SET @rndLastNameStr = @rndLastNameStr + CHAR(ROUND(((122 - 97 -1) * RAND() + 97), 0))

   SET @x = @x+1

  END

 INSERT INTO @reps (repID, repFirstName, repLastName) VALUES

 (@i, @rndFirstNameStr, @rndLastNameStr)

  SET @i += 1

END

 

SET @i =  0

 

WHILE @i < 100

BEGIN

 SET @x = ROUND(((15 - 10 -1) * RAND() + 10), 0)

 SET @rndFirstNameStr = ''

  WHILE @x < 20

  BEGIN

   IF LEN(@rndFirstNameStr) = 0 SET @rndFirstNameStr = @rndFirstNameStr + CHAR(ROUND(((91 - 65 -1) * RAND() + 65), 0)) ELSE SET @rndFirstNameStr = @rndFirstNameStr + CHAR(ROUND(((122 - 97 -1) * RAND() + 97), 0))

   SET @x = @x+1

  END

 SET @x = ROUND(((15 - 10 -1) * RAND() + 10), 0)

 SET @rndLastNameStr = ''

  WHILE @x < 20

  BEGIN

   IF LEN(@rndLastNameStr) = 0 SET @rndLastNameStr = @rndLastNameStr + CHAR(ROUND(((91 - 65 -1) * RAND() + 65), 0)) ELSE SET @rndLastNameStr = @rndLastNameStr + CHAR(ROUND(((122 - 97 -1) * RAND() + 97), 0))

   SET @x = @x+1

  END

 SET @x = ROUND(((15 - 10 -1) * RAND() + 10), 0)

 SET @rndCompanyNameStr = ''

  WHILE @x < 20

  BEGIN

   IF LEN(@rndCompanyNameStr) = 0 SET @rndCompanyNameStr = @rndCompanyNameStr + CHAR(ROUND(((91 - 65 -1) * RAND() + 65), 0)) ELSE SET @rndCompanyNameStr = @rndCompanyNameStr + CHAR(ROUND(((122 - 97 -1) * RAND() + 97), 0))

   SET @x = @x+1

  END

 INSERT INTO @customers (customerID, customerFirstName, customerLastName, customerCompanyName) VALUES

 (@i, @rndFirstNameStr, @rndLastNameStr, @rndCompanyNameStr)

  SET @i += 1

END

 

SET @i =  0

 

WHILE @i < 200

BEGIN

 SET @rndInvoiceSeq += 1

 SET @rndQty = ROUND(((5 - 1 -1) * RAND() + 1), 0)

 SET @rndItemID = ROUND(((50 - 1 -1) * RAND() + 1), 0) - 1

 INSERT INTO @invoices (invoiceID, invoiceSeqID, orderDate, customerID, repID, itemID, qty)

 VALUES  (@i, @rndInvoiceSeq, @rndOrderDate, @rndCustomerID, @rndRepID, @rndItemID, @rndQty)

 IF @rndQty % 2 = 1

 BEGIN

  SET @i += 1

  SET @rndInvoiceSeq = 0

  SET @rndOrderDate = DATEADD(DAY, -ROUND(((365 - 0 -1) * RAND() + 0), 0), CURRENT_TIMESTAMP)

  SET @rndCustomerID = ROUND(((100 - 1 -1) * RAND() + 1), 0) - 1

  SET @rndRepID = ROUND(((20 - 1 -1) * RAND() + 1), 0) - 1

 END

END

SET NOCOUNT OFF

 

SELECT *

  FROM @invoices i

    LEFT OUTER JOIN @customers c

         ON i.customerID = c.customerID

    LEFT OUTER JOIN @reps r

         ON i.repID = r.repID

    LEFT OUTER JOIN @items it

         ON i.itemID = it.itemID

 

Each time we run this script, it will create and populate invoices, customers, items and reps. The schema could easily be expanded, but this should be enough for our needs.

Using our test data, we can take a look to see how long our customers take between orders.

;WITH daysBetweenOrders AS (

SELECT c.customerID, c.customerFirstName, c.customerLastName, i.invoiceDate, i.invoiceID

  FROM @customers c

    LEFT OUTER JOIN @invoices i

         ON c.customerID = i.customerID

 GROUP BY c.customerID, c.customerFirstName, c.customerLastName, i.invoiceDate, i.invoiceID

)

 

SELECT d1.customerID, d1.customerFirstName, d1.customerLastName, d1.invoiceID AS thisInvoiceID, d1.invoiceDate AS thisInvoiceDate, d2.invoiceID AS nextInvoiceID, d2.invoiceDate AS nextInvoiceDate,

       DATEDIFF(DAY, d1.invoiceDate, d2.invoiceDate) AS daysBetweenOrders

  FROM daysBetweenOrders d1

    LEFT OUTER JOIN daysBetweenOrders d2

         ON d1.customerID = d2.customerID

         AND d2.invoiceDate = (SELECT MIN(invoiceDate) FROM @invoices WHERE customerID = d1.customerID and invoiceDate > d1.invoiceDate)

 

Great. Now we know how many calendar days between orders. But is this really the metric we wanted? Customers can only place orders while we’re open. Let’s say we’re only open between Monday and Friday, and never on holidays. Is it really right to state the number of calendar days when there’s no way for the customer to place an order on some of those days?

Somehow, we’ll have to figure out which days we need to exclude, and remove them from the difference. Enter the calendar table!

;WITH daysBetweenOrders AS (

SELECT c.customerID, c.customerFirstName, c.customerLastName, i.invoiceDate, i.invoiceID

  FROM @customers c

    LEFT OUTER JOIN @invoices i

         ON c.customerID = i.customerID

 GROUP BY c.customerID, c.customerFirstName, c.customerLastName, i.invoiceDate, i.invoiceID

)

 

SELECT d1.customerID, d1.customerFirstName, d1.customerLastName, d1.invoiceID AS thisInvoiceID, d1.invoiceDate AS thisInvoiceDate, d2.invoiceID AS nextInvoiceID, d2.invoiceDate AS nextInvoiceDate,

       DATEDIFF(DAY, d1.invoiceDate, d2.invoiceDate) AS calendarDaysBetweenOrders,

       NULLIF(COUNT(c.today),0) AS businessDaysBetweenOrders

  FROM daysBetweenOrders d1

    LEFT OUTER JOIN daysBetweenOrders d2

         ON d1.customerID = d2.customerID

         AND d2.invoiceDate = (SELECT MIN(invoiceDate) FROM @invoices WHERE customerID = d1.customerID and invoiceDate > d1.invoiceDate)

       LEFT OUTER JOIN calendar c

         ON c.today BETWEEN d1.invoiceDate AND d2.invoiceDate

         AND c.isHoliday = 0

         AND c.weekDay <> 1 

         AND c.weekDay <> 7

 GROUP BY d1.customerID, d1.customerFirstName, d1.customerLastName, d1.invoiceID, d1.invoiceDate, d2.invoiceID, d2.invoiceDate

 

Adding in the calendar table lets us filter the result, excluding predefined holidays, as well as weekends, without the need for pesky date parts.

If you already have a calendar table, go ahead and look at the result. It’ll show you the discrepancy between the two date differences. The longer the range is the wider the gap will be.

If you don’t have a calendar table, read on!

 

Getting to the point 

To build a calendar table, I use three functions. These functions do all of the heavy lifting required to make all the calculations we need about any given date.

The first of these three is

phNthDayOfMonth

CREATE FUNCTION dbo.phNthDayOfMonth (@year INT, @month SMALLINT, @weekday VARCHAR(15), @nth SMALLINT)

 RETURNS DATETIME

 AS

 BEGIN

 DECLARE @the_date DATETIME, @c_date DATETIME, @cth SMALLINT

 SET @cth = 0

 SET @c_date = CONVERT(VARCHAR,@year)+'-'+CONVERT(VARCHAR,@MONTH)+'-01'

 WHILE MONTH(@c_date) = @MONTH

  BEGIN

  IF DATENAME(WEEKDAY,@c_date) = @weekday SET @cth = @cth + 1

  IF @cth = @nth and DATENAME(WEEKDAY,@c_date) = @weekday SET @the_date = @c_date

  SET @c_date = DATEADD(DAY,1,@c_date)

  END

  IF @the_date is null

  BEGIN

   SET @the_date = DATEADD(DAY, DATEPART(WEEKDAY,DATEADD(MONTH,1,CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01'))-7,DATEADD(MONTH,1,CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01'))

  END

RETURN @the_date

END

 

It accepts as parameters a year, month weekday name and the Nth value you want.

Passing it 2015, 1, ‘Monday’, 1 causes it to return 2015-01-05, as January 5th was the first Monday in 2015. Passing it 2015, 1, ‘Monday’, 5 causes it to return 2015-01-26, as January 26th was the last Monday in 2015, it was also the 4th Monday in 2015, but the function is smart enough to return the last possible answer for the month in question in the event that you pass a larger Nth value than occurred. If there were 5 Mondays in January, it would have given you the 5th. In December 2014 there were, so let’s try:

SELECT dbo.phNthDayOfMonth(2014, 12, 'Monday', 5) AS fifth, dbo.phNthDayOfMonth(2014, 12, 'Monday', 4) AS fourth, dbo.phNthDayOfMonth(2014, 12, 'Monday', 1) AS first

 

Hooray! What am I getting at and why do we care about Nth days? We’ll use this function to help us figure out when those holidays which fall on the Nth weekday of a month, like Thanks-giving, or Labor Day.

Next up,  phHolidays. This function finds all the holidays for any given year, and returns them as a table. It needs to be customized to your particular region. In this case it’s configured for Saskatchewan, Canada. Altering the holidays to meet your own needs isn’t too complicated, unless you have things calculated on the lunar cycle, such as Easter. Lucky for you, I did the legwork on that one already.

phHolidays

CREATE FUNCTION phHolidays(@year INT)

RETURNS @table TABLE

(

 date DATETIME,

 type VARCHAR(10),

 name VARCHAR(25)

)

AS

BEGIN

INSERT INTO @table

SELECT CONVERT(DATETIME,CONVERT(VARCHAR,@year)+'-01-01') AS date,'Holiday' AS type ,'New Years Day' AS name UNION ALL

SELECT dbo.phNthDayOfMonth(@year,2, 'Monday',3),'Holiday','Family Day' UNION ALL

SELECT DATEADD(d,0-CASE WHEN DATEPART(WEEKDAY,CONVERT(VARCHAR,@year)+'-05-25') in (1,2) THEN 5+DATEPART(WEEKDAY,CONVERT(VARCHAR,@year)+'-05-25') ELSE DATEPART(WEEKDAY,CONVERT(VARCHAR,@year)+'-05-25')-1 END, CONVERT(VARCHAR,@year)+'-05-25') ,'Holiday','Victoria Day' UNION ALL

SELECT CONVERT(VARCHAR,@year)+'-07-01','Holiday','Canada Day' UNION ALL

SELECT dbo.phNthDayOfMonth(@year,8, 'Monday',1),'Holiday','Civic Holiday' UNION ALL

SELECT dbo.phNthDayOfMonth(@year,9, 'Monday',1),'Holiday','Labour Day' UNION ALL

SELECT dbo.phNthDayofMonth(@year,10,'Monday',2),'Holiday','Thanksgiving' UNION ALL

SELECT CONVERT(VARCHAR,@year)+'-11-11','Holiday','Rememberance Day'UNION ALL

SELECT CONVERT(VARCHAR,@year)+'-12-25','Holiday','Christmas Day'   UNION ALL

SELECT CONVERT(VARCHAR,@year)+'-12-26','Holiday','Boxing Day' UNION ALL

SELECT CONVERT(DATETIME,CONVERT(VARCHAR,@year) + '-0'+CONVERT(VARCHAR, FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25))+'-'+CONVERT(VARCHAR,CONVERT(INT,(((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + (FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25)) + 19) % 32,0))),'Holiday','Easter Sunday' UNION ALL

SELECT DATEADD(DAY,-2,CONVERT(DATETIME,CONVERT(VARCHAR,@year) + '-0'+CONVERT(VARCHAR, FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25))+'-'+CONVERT(VARCHAR,CONVERT(INT,(((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + (FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25)) + 19) % 32,0)))),'Holiday','Good Friday' UNION ALL

SELECT DATEADD(DAY,1,CONVERT(DATETIME,CONVERT(VARCHAR,@year) + '-0'+CONVERT(VARCHAR, FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25))+'-'+CONVERT(VARCHAR,CONVERT(INT,(((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + (FLOOR((((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) - (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) + ((((2 * FLOOR((@year / 100.0)) % 4) + (2*((@year % 100) / 4))) - ((@year % 100) % 4) - ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30) + (FLOOR((@year % 19.0 + (11 * ((( (19 * (@year % 19.0)) + FLOOR(@year / 100.0)) - FLOOR((@year / 100.0) / 4) - FLOOR((8 * (@year / 100.0) + 13) / 25.0) + 15) % 30))) / 319)) +32) % 7) + 90) / 25)) + 19) % 32,0)))),'Holiday','Easter Monday'

 

UPDATE @table

SET date =

CASE WHEN MONTH(date) = 12 AND name != 'Boxing Day' and DATEPART(WEEKDAY,date) = 7 THEN DATEADD(DAY,2,date)

     WHEN MONTH(date) = 12 AND name != 'Boxing Day' and DATEPART(WEEKDAY,date) = 1 THEN DATEADD(DAY,1,date)

     WHEN MONTH(date) = 12 AND name = 'Boxing Day' and DATEPART(WEEKDAY,date) = 7 THEN DATEADD(DAY,2,date)

     WHEN MONTH(date) = 12 AND name = 'Boxing Day' and DATEPART(WEEKDAY,date) = 1 THEN DATEADD(DAY,2,date)

     WHEN MONTH(date) = 12 AND name = 'Boxing Day' and DATEPART(WEEKDAY,date) = 2 THEN DATEADD(DAY,1,date)

     ELSE date

END

 

RETURN

END

 

Yeah. Easter sucks.

The function only requires you to pass it the year in question, and gives you back all the holidays as defined within it. Essentially there are two types of holiday. Those that fall on the Nth day of a month, and those that always fall on the same day.

If you have a set-date holiday, like Canada day, you can define it like so:

SELECT CONVERT(VARCHAR,@year)+'-07-01','Holiday','Canada Day'

Yep, we literally just stuffed the year onto the front of the date and called it done. That was easy. More about these holidays later.

If your holiday is the Nth day of a month, like Family day, which is the third Monday in Febuary, we can call on our friend phNthDayOfTheMonth:

SELECT dbo.phNthDayOfMonth(@year,2, 'Monday',3),'Holiday','Family Day'

Our handy dandy function does the work, and gives us back the right date. If you have super complicated holidays you need to calculate, you’re on your own from here on out. I don’t pretend to understand how the Easter calculation works; it was ported from an anonymous posting in another language. It has been thoroughly tested and seems to line up with the next 30 years or so.

You'll notice there is a section at the end of the function which does some manual updating. Sometimes set date holidays fall on the weekend. These are typically pushed to the next week day. If Boxing day is on a Saturday, it's observed on the following Monday, for example.

So, now we know about the holidays in any given year, what about all the rest of the data for a date?

This is where the big one comes in. It’s not terribly clever, or complicated, it’s just a lot of code. Here’s phCalendar

phCalendar

CREATE FUNCTION phC​alendar(@date DATETIME)

RETURNS @table TABLE (now DATETIME, today DATETIME, monthStart DATETIME, monthEnd DATETIME, prevMonthStart DATETIME, prevMonthEnd DATETIME, weekStart DATETIME, weekEnd DATETIME, prevWeekStart DATETIME, prevWeekEnd DATETIME, quarterStart DATETIME, quarterEnd DATETIME, prevQuarterStart DATETIME, prevQuarterEnd DATETIME, yearStart DATETIME, yearEnd DATETIME, prevYearStart DATETIME, prevYearEnd DATETIME, monthEndTS DATETIME, prevMonthEndTS DATETIME, weekEndTS DATETIME, prevWeekEndTS DATETIME, quarterEndTS DATETIME, prevQuarterEndTS DATETIME, yearEndTS DATETIME, prevYearEndTS DATETIME, year SMALLINT, month SMALLINT, day SMALLINT, monthName VARCHAR(15), dayName VARCHAR(15), weekDay SMALLINT, ISOWeekNumber INT, weekNumber INT, isHoliday BIT, holidayName VARCHAR(100))

AS

BEGIN

IF @date IS NULL SET @date = getdate()

INSERT INTO @table

 

SELECT

@date AS now,

CONVERT(DATETIME,CONVERT(VARCHAR,@date,101)) AS today,

DATEADD(DAY,0-DAY(@date)+1,CONVERT(DATETIME,CONVERT(VARCHAR,@date,101))) AS monthStart,

DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,0-DAY(@date)+1,CONVERT(DATETIME,CONVERT(VARCHAR,@date,101))))) AS monthEnd,

DATEADD(MONTH,-1,DATEADD(DAY,0-DAY(@date)+1,CONVERT(DATETIME,CONVERT(VARCHAR,@date,101)))) AS prevMonthStart,

DATEADD(DAY,-1-DAY(@date)+1,CONVERT(DATETIME,CONVERT(VARCHAR,@date,101))) AS prevMonthEnd,

DATEADD(DAY,1-DATEPART(DW,@date),CONVERT(DATETIME,CONVERT(VARCHAR,@date,101))) AS weekStart,

DATEADD(DAY,7-DATEPART(DW,@date),CONVERT(DATETIME,CONVERT(VARCHAR,@date,101))) AS weekEnd,

DATEADD(DAY,-6-DATEPART(DW,@date),CONVERT(DATETIME,CONVERT(VARCHAR,@date,101))) AS prevWeekStart,

DATEADD(DAY,0-DATEPART(DW,@date),CONVERT(DATETIME,CONVERT(VARCHAR,@date,101))) AS prevWeekEnd,

CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@date)) +'-'+ RIGHT('0'+CONVERT(VARCHAR,((DATEPART(QUARTER,@date)-1)\*3)+1),2)+'-01') AS quarterStart,

DATEADD(DAY,-1,DATEADD(QUARTER,1,CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@date)) +'-'+ RIGHT('0'+CONVERT(VARCHAR,((DATEPART(QUARTER,@date)-1)\*3)+1),2)+'-01'))) AS quarterEnd,

CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(DATEADD(QUARTER,-1,@date))) +'-'+ RIGHT('0'+CONVERT(VARCHAR,((DATEPART(QUARTER,DATEADD(QUARTER,-1,@date))-1)\*3)+1),2)+'-01') AS prevquarterStart,

DATEADD(DAY,-1,DATEADD(QUARTER,1,CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(DATEADD(QUARTER,-1,@date))) +'-'+ RIGHT('0'+CONVERT(VARCHAR,((DATEPART(QUARTER,DATEADD(QUARTER,-1,@date))-1)\*3)+1),2)+'-01'))) AS prevQuarterEnd,

DATEADD(DAY,1-DAY(@date),DATEADD(MONTH,1-MONTH(@date),CONVERT(VARCHAR,@date,101))) AS yearStart,

DATEADD(YEAR,1,DATEADD(DAY,0-DAY(@date),DATEADD(MONTH,1-MONTH(@date),CONVERT(VARCHAR,@date,101)))) AS yearEnd,

DATEADD(YEAR,-1,DATEADD(DAY,1-DAY(@date),DATEADD(MONTH,1-MONTH(@date),CONVERT(VARCHAR,@date,101)))) AS prevYearStart,

DATEADD(YEAR,-1,DATEADD(YEAR,1,DATEADD(DAY,0-DAY(@date),DATEADD(MONTH,1-MONTH(@date),CONVERT(VARCHAR,@date,101))))) AS prevYearEnd,

DATEADD(MS,-3,DATEADD(DAY,0,DATEADD(MONTH,1,DATEADD(DAY,0-DAY(@date)+1,CONVERT(DATETIME,CONVERT(VARCHAR,@date,101)))))) AS monthEndTS,

DATEADD(MS,-3,DATEADD(DAY,-1-DAY(@date)+2,CONVERT(DATETIME,CONVERT(VARCHAR,@date,101)))) AS prevMonthEndTS,

DATEADD(MS,-3,DATEADD(DAY,8-DATEPART(DW,@date),CONVERT(DATETIME,CONVERT(VARCHAR,@date,101)))) AS weekEndTS,

DATEADD(MS,-3,DATEADD(DAY,1-DATEPART(DW,@date),CONVERT(DATETIME,CONVERT(VARCHAR,@date,101)))) AS prevWeekEndTS,

DATEADD(MS,-3,DATEADD(DAY,0,DATEADD(QUARTER,1,CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@date)) +'-'+ RIGHT('0'+CONVERT(VARCHAR,((DATEPART(QUARTER,@date)-1)\*3)+1),2)+'-01')))) AS quarterEndTS,

DATEADD(MS,-3,DATEADD(DAY,0,DATEADD(QUARTER,1,CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(DATEADD(QUARTER,-1,@date))) +'-'+ RIGHT('0'+CONVERT(VARCHAR,((DATEPART(QUARTER,DATEADD(QUARTER,-1,@date))-1)\*3)+1),2)+'-01')))) AS prevQuarterEndTS,

DATEADD(MS,-3,DATEADD(YEAR,1,DATEADD(DAY,1-DAY(@date),DATEADD(MONTH,1-MONTH(@date),CONVERT(VARCHAR,@date,101))))) AS yearEndTS,

DATEADD(MS,-3,DATEADD(DAY,1-DAY(@date),DATEADD(MONTH,1-MONTH(@date),CONVERT(VARCHAR,@date,101)))) AS prevYearEndTS,

YEAR(@date) AS year,

MONTH(@date) AS month,

DAY(@Date) AS day,

DATENAME(MONTH,@Date) AS monthName,

DATENAME(WEEKDAY,@date) AS dayName,

DATEPART(WEEKDAY,@date) AS weekDay,

CASE WHEN @date BETWEEN DATEADD(DAY,-4,dbo.phNthDayOfMonth(YEAR(@date)+CASE WHEN MONTH(@date) = 12 THEN 1 ELSE 0 END,1,'Thursday' ,1)) AND DATEADD(DAY,2,dbo.phNthDayOfMonth(YEAR(@date)+CASE WHEN MONTH(@date) = 12 THEN 1 ELSE 0 END,1,'Thursday',1)) AND DATEPART(WEEK,@date) = 53 THEN 1

 WHEN @date NOT BETWEEN DATEADD(DAY,-4,dbo.phNthDayOfMonth(YEAR(@date)+CASE WHEN MONTH(@date) = 12 THEN 1 ELSE 0 END,1,'Thursday',1)) AND DATEADD(DAY,2,dbo.phNthDayOfMonth(YEAR(@date)+CASE WHEN MONTH(@date) = 12 THEN 1 ELSE 0 END,1,'Thursday',1)) AND DATEPART(WEEK,@date) = 1 THEN 53

ELSE DATEPART(WEEK,@date) END AS isoWeekNumber,

DATEPART(WEEK,@date) AS weekNumber,

COALESCE((SELECT 1 from dbo.phHolidays(DATEPART(YEAR,@date)) WHERE date = @date),0) AS isHoliday,

(SELECT name from dbo.phHolidays(DATEPART(YEAR,@date)) WHERE date = @date) AS holidayName

 

RETURN

END

 

It accepts a date, and returns a pile of information about it, including if it’s a holiday, what Day, month, year, when the nearest end of month is, when the month it’s in runs from, where the month preceding it was and so on.

Now, you can use it stand alone, as a function, or you can opt to use it to fill a permanent table. Doing so just takes a simple loop:

Filling the table

SET NOCOUNT ON

DECLARE @endDate DATETIME, @date DATETIME

SET @endDate = '2014-12-31'

SET @date = '2014-01-01'

 

SELECT *

 INTO calendar

  FROM phDates(@date)

 

WHILE @date <= @endDate

BEGIN

 SET @date = DATEADD(DAY,1,@date)

 INSERT INTO calendar (now, today, monthStart, monthEnd, prevMonthStart, prevMonthEnd, weekStart, weekEnd, prevWeekStart, prevWeekEnd, quarterStart, quarterEnd, prevQuarterStart, prevQuarterEnd, yearStart, yearEnd, prevYearStart, prevYearEnd, monthEndTS, prevMonthEndTS, weekEndTS, prevWeekEndTS, quarterEndTS, prevQuarterEndTS, yearEndTS, prevYearEndTS, year, month, day, monthName, dayName, weekDay, ISOWeekNumber, weekNumber, isHoliday, holidayName)

 SELECT now, today, monthStart, monthEnd, prevMonthStart, prevMonthEnd, weekStart, weekEnd, prevWeekStart, prevWeekEnd, quarterStart, quarterEnd, prevQuarterStart, prevQuarterEnd, yearStart, yearEnd, prevYearStart, prevYearEnd, monthEndTS, prevMonthEndTS, weekEndTS, prevWeekEndTS, quarterEndTS, prevQuarterEndTS, yearEndTS, prevYearEndTS, year, month, day, monthName, dayName, weekDay, ISOWeekNumber, weekNumber, isHoliday, holidayName

   FROM phDates(@date)

END

SET NOCOUNT OFF

 

And there you have it. The loop will run between the dates you specify, and populate a table with all that lovely calendar data.

Putting it to use:

Using our example data from the beginning let’s have a play:

Number of invoices per week:

SELECT COUNT(*) AS invoices, c.weekStart

  FROM @invoices i

    INNER JOIN calendar c

         ON i.invoiceDate = c.today

         AND i.invoiceSeqID = 1

GROUP BY c.weekStart

 

Sales per month:

SELECT SUM(it.Price) AS sales, c.monthStart

  FROM @invoices i

    INNER JOIN @items it

         ON i.itemID = it.itemID

    INNER JOIN calendar c

         ON i.invoiceDate = c.today

GROUP BY c.monthStart

Sales on non-business days:

 

SELECT SUM(it.Price) AS sales, COUNT(DISTINCT i.invoiceID) AS invoices, COALESCE(c.holidayName,c.dayName)

  FROM @invoices i

    INNER JOIN @items it

         ON i.itemID = it.itemID

    INNER JOIN calendar c

         ON i.invoiceDate = c.today

         AND i.invoiceSeqID = 1

         AND (

              c.weekDay IN (1,7)

          OR  c.isHoliday = 1)

GROUP BY COALESCE(c.holidayName,c.dayName)

 

Percent change over month:

;WITH monthlySales AS (

SELECT SUM(it.Price) AS sales, c.monthStart, c.prevMonthStart

  FROM @invoices i

    INNER JOIN @items it

         ON i.itemID = it.itemID

    INNER JOIN calendar c

         ON i.invoiceDate = c.today

GROUP BY c.monthStart, c.prevMonthStart

)

 

SELECT ms.monthStart, ms.sales, ms.prevMonthStart, pms.sales, ((ms.sales - pms.sales) / pms.sales) * 100 AS percentChange

  FROM monthlySales ms

    LEFT OUTER JOIN monthlySales pms

         ON ms.prevMonthStart = pms.monthStart

 

These are just a few simple examples of how having the calendar table can make your life easier.

You can customize your calendar table as much as you need to, adding new columns (fiscal quarters perhaps?) and metrics as the need arises.