Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Calendar Tables

By Todd Fifield, (first published: 2010/07/15)

I've been using Calendar tables for years to create code that is easier to read, performs better and is more flexible than using complicated date type calculations in queries and stored procedures. I recently saw a posting here on SQLServerCentral where the original poster asked how to improve his query's performance. The suggestions did improve performance, but I thought that a very different approach would have served better and would allow further queries of similar type to be written much more easily.

In this article I will be using a month based calendar table. I also use week based and quarter based calendar tables (the quarter based tables make life much easier when doing 4-4-5 accounting periods, which are based on 13 weeks rather than Calendar months).

The theory behind this type of calendar table is that the table itself determines the bucket into which date related facts are dumped or aggregated. Date calculations mostly are not used. The table has an ascending integer (usually an identity), which is the clustered index. It has a start date for the period and either an end date for the period or the start of the next period. These are DATETIME data types. If the dates you use in your data are pure dates (no time) then you would use an end date and it would be the last day of the period. If there is time in your dates then you would use the start of the next period instead of an end date.

More data can be put into a month based calendar table than I'm using here, but this should do for our purposes. Here is the code to create a simple month based calendar table starting at January 2000 going through December 2024:

-- These columns are the most useful. Others can be added.

SET NOCOUNT ON;
CREATE TABLE CalMonth
( MonthID INT IDENTITY(1, 1)
, MonthStart DATETIME
, NextMonth DATETIME
, MonthDescr CHAR(6)
, MonthName CHAR(3)
, YearMonth INT
, MonthNum INT
);

DECLARE
 @MonthStart DATETIME
, @NextMonth DATETIME
, @MonthDescr CHAR(6)
, @MonthName CHAR(3)
, @MonthNum INT
, @Year INT
, @YearMonth INT;

SET @MonthStart = '1/1/2000';
SET @NextMonth = DATEADD(month, 1, @MonthStart);

WHILE @MonthStart < '1/1/2025'
 BEGIN
 SET @MonthNum = DATEPART(month, @MonthStart);
 SET @MonthName = CASE @MonthNum
 WHEN 1 THEN 'JAN'
 WHEN 2 THEN 'FEB'
 WHEN 3 THEN 'MAR'
 WHEN 4 THEN 'APR'
 WHEN 5 THEN 'MAY'
 WHEN 6 THEN 'JUN'
 WHEN 7 THEN 'JUL'
 WHEN 8 THEN 'AUG'
 WHEN 9 THEN 'SEP'
 WHEN 10 THEN 'OCT'
 WHEN 11 THEN 'NOV'
 ELSE 'DEC' END;
 SET @Year = DATEPART(year, @MonthStart);
 SET @YearMonth = (@Year * 100) + @MonthNum;
 SET @MonthDescr = @MonthName + '-' + RIGHT(CONVERT(VARCHAR, @Year), 2);

INSERT INTO CalMonth
 ( MonthStart, NextMonth, MonthDescr, MonthName, YearMonth, MonthNum )
 SELECT
 @MonthStart, @NextMonth, @MonthDescr, @MonthName, @YearMonth, @MonthNum;

SET @MonthStart = @NextMonth;
SET @NextMonth = DATEADD(month, 1, @NextMonth);
END
GO

ALTER TABLE [dbo].[CalMonth] ADD CONSTRAINT [PK_CalMonth] PRIMARY KEY CLUSTERED 
(
 MonthID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF
, FILLFACTOR = 100) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [CalMonth_Dates] ON [dbo].[CalMonth] 
(
 [MonthStart] ASC,
 [NextMonth] ASC
)
INCLUDE ( [MonthDescr]) 
WITH (PAD_INDEX = OFF, FILLFACTOR = 100)
GO

This table should fit into 2 pages total. The idea is that the calendar table should be as compact as you can make it to allow it to be cached in memory as much as possible.

Here is a simple example of using the CalMonth table in a query in the AdventureWorks database.( I had to dummy up the date for the query since my SalesOrderHeader table is way out of date). It returns a range of customers with their total sales by month for the month of June 2004 and two months before that. Notice that there is no DATEADD or DATEPART in the query. Since the MonthID is a sequential integer, 2 is subtracted from the latest month to get the first month. The display month is also fetched from CalMonth.

DECLARE
 @LatestMonth INT;

SELECT @LatestMonth = MonthID
FROM CalMonth
WHERE MonthStart = '2004-06-01'

SELECT C.CustomerID, C.AccountNumber, C.TerritoryID
, CM.MonthID, CM.MonthDescr
, COUNT(*) AS NumberOfSales
, SUM(SO.SubTotal) AS MonthlySales
FROM Sales.Customer C
INNER JOIN Sales.SalesOrderHeader SO ON
 C.CustomerID = SO.CustomerID
INNER JOIN CalMonth CM ON
 SO.OrderDate >= CM.MonthStart
 AND SO.OrderDate < CM.NextMonth
WHERE C.CustomerType = 'S'
AND CM.MonthID BETWEEN @LatestMonth - 2 And @LatestMonth
GROUP BY C.CustomerID, C.AccountNumber, C.TerritoryID
, CM.MonthID, CM.MonthDescr
ORDER BY C.TerritoryID, CM.MonthID;

The result set returned looks like:

CustomerID  AccountNumber TerritoryID MonthID     MonthDescr NumberOfSales MonthlySales
----------- ------------- ----------- ----------- ---------- ------------- ------------
110         AW00000110    1           52          APR-04     1             75997.3582
146         AW00000146    1           52          APR-04     1             85828.7208
290         AW00000290    1           52          APR-04     1             66245.5197
253         AW00000253    1           52          APR-04     1             4372.7986
325         AW00000325    1           52          APR-04     1             318.4545
344         AW00000344    1           52          APR-04     1             59.9326
380         AW00000380    1           52          APR-04     1             272.961
398         AW00000398    1           52          APR-04     1             1655.4936
451         AW00000451    1           52          APR-04     1             50000.0044
505         AW00000505    1           52          APR-04     1             60748.8992
506         AW00000506    1           52          APR-04     1             113252.4963
560         AW00000560    1           52          APR-04     1             452.7394
578         AW00000578    1           52          APR-04     1             299.6926
668         AW00000668    1           52          APR-04     1             8687.2228
673         AW00000673    1           52          APR-04     1             2312.6134
2           AW00000002    1           53          MAY-04     1             822.009

CalMonth is used to both select the sales orders to include and for the grouping. The MonthDescr makes it easy to display which month we're grouping.

I did some testing on an alternate version of this query where I just set the start and end dates as variables and then did a GROUP BY (DATEPART(year, OrderDate) * 100) + DATEPART(month, OrderDate) in order to group by Customer, Year and Month and eliminated the CalMonth table. It generally ran 10 ms longer in CPU time. So this method also affords good performance .

Now we get to more complex applications of our CalMonth table. This is from an actual post here on SQL Server Central just a few weeks ago. I slightly modified it to run in the AdventureWorks database.

The original poster (OP) wanted a side by side comparison of transactions from 2 different years summarized and compared month by month. This was for a hotel group. There were close to 2 million records in the Stay (transaction) table.

Here is a script that will create a 2 million record temp table that we can use for comparison:

IF OBJECT_ID('tempdb..#Stay') IS NOT NULL
 DROP TABLE #Stay;

CREATE TABLE #Stay(
 [StayID] [uniqueidentifier] NOT NULL,
 [GuestID] [uniqueidentifier] NOT NULL,
 [ArrivalDate] [datetime] NOT NULL,
 [TotalRevenue] [money] NOT NULL
 CONSTRAINT [PK_Stay] PRIMARY KEY NONCLUSTERED 
(
 [StayID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

ALTER TABLE #Stay ADD CONSTRAINT [DF_Stay_StayID] DEFAULT (newsequentialid()) FOR [StayID];
CREATE CLUSTERED INDEX [IX_Stay_ArrivalDateGuestID] ON #Stay
(
 [ArrivalDate] ASC,
 [GuestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

--Populate with one million rows of random data, thanks to Jeff for this (http://www.sqlservercentral.com/articles/Crosstab/65048/)
INSERT INTO #Stay (GuestID, ArrivalDate, TotalRevenue)
SELECT TOP 2000000
 NEWID(),
 CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
 CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY)
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2

I added a covering index on ArrivalDate and Revenue in order to avoid excessive key lookups:

CREATE INDEX #Stay_ArrivalRevenue ON #STAY (ArrivalDate, TotalRevenue)

The technique the OP was using required a Tally or Numbers table. Here is a link to a great article by Jeff Moden on the use of numbers tables: http://www.sqlservercentral.com/articles/T-SQL/62867/

Here is the code to generate a Tally table:

SELECT TOP 10000
N = IDENTITY(INT, 1, 1)
INTO Tally
FROM sys.syscolumns S1
CROSS JOIN sys.syscolumns S2
GO
ALTER TABLE Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED (N)
WITH FILLFACTOR = 100
GO

Here is the query the OP wanted help on. The tally table was used for grouping to get a single month and in the date calculations:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DECLARE @StartYear1 DATETIME;
DECLARE @StartYear2 DATETIME;

SET @StartYear2 = DATEADD(YY, -1, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0));
SET @StartYear1 = DATEADD(YY, -1, @StartYear2);

SELECT 
 DATEADD(MM, N - 1, @StartYear1) Year1,
 SUM(CASE WHEN ArrivalDate >= @StartYear1 AND ArrivalDate < @StartYear2 THEN TotalRevenue ELSE 0 END) Year1Revenue,
 DATEADD(MM, N -1, @StartYear2) Year2,
 SUM(CASE WHEN ArrivalDate >= @StartYear2 THEN TotalRevenue ELSE 0 END) Year2Revenue
FROM Tally
LEFT JOIN #Stay 
ON 
 DATEADD(MM, N - 1, @StartYear1) = DATEADD(MM, DATEDIFF(MM, 0, ArrivalDate), 0)
 OR DATEADD(MM, N - 1, @StartYear2) = DATEADD(MM, DATEDIFF(MM, 0, ArrivalDate), 0)
WHERE ArrivalDate >= @StartYear1 AND N <= 12
GROUP BY N
ORDER BY Year1

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

The result set returned form this query was:

Year1                   Year1Rev  Year2                   Year2Revenue
2008-06-01 00:00:00.000 814652.29 2009-06-01 00:00:00.000 815557.55
2008-07-01 00:00:00.000 850882.41 2009-07-01 00:00:00.000 831792.32
2008-08-01 00:00:00.000 844237.14 2009-08-01 00:00:00.000 837465.42
2008-09-01 00:00:00.000 836915.91 2009-09-01 00:00:00.000 833296.03
2008-10-01 00:00:00.000 838332.34 2009-10-01 00:00:00.000 855292.19
2008-11-01 00:00:00.000 819412.58 2009-11-01 00:00:00.000 827802.45
2008-12-01 00:00:00.000 838095.67 2009-12-01 00:00:00.000 851131.06
2009-01-01 00:00:00.000 858064.48 2010-01-01 00:00:00.000 0.00
2009-02-01 00:00:00.000 771925.00 2010-02-01 00:00:00.000 0.00
2009-03-01 00:00:00.000 855450.64 2010-03-01 00:00:00.000 0.00
2009-04-01 00:00:00.000 821391.97 2010-04-01 00:00:00.000 0.00
2009-05-01 00:00:00.000 853181.60 2010-05-01 00:00:00.000 0.00

As you can see there are numerous calculations to get the dates right for the grouping and selecting. These types of date calculations prevent the optimizer from using the index on ArrivalDate. The average CPU time for this query on my humble box was 3827 ms. It also isn't the easiest query in the world to read and understand.

Here is another solution using the CalMonth table:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DECLARE 
 @StartYear2 DATETIME
, @StartMonth INT

-- This returns the first day of the current month.
SET @StartYear2 = DATEADD(YY, -1, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0));
-- Get the MonthID for this month.
SELECT 
 @StartMonth = MonthID
FROM CalMonth
WHERE MonthStart = @StartYear2;

-- Set to same month the year before.
SET @StartMonth = @StartMonth - 12

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

SELECT
 X.MonthName + '-' + MIN(X.YrSuffix) AS FirstYearMonth
, SUM(CASE WHEN YearNo = 1 THEN X.TotalRevenue ELSE 0 END) AS Year1Revenue
, X.MonthName + '-' + MAX(X.YrSuffix) AS SecondYearMonth
, SUM(CASE WHEN YearNo = 2 THEN X.TotalRevenue ELSE 0 END) as Year2Revenue
, X.MonthOrd
FROM
 (SELECT CM.MonthName, S.TotalRevenue AS TotalRevenue, 1 AS YearNo
 , CM.MonthID - @StartMonth AS MonthOrd
 , RIGHT(CONVERT(VARCHAR, DATEPART(year, CM.MonthStart)), 2) AS YrSuffix
 FROM CalMonth CM
 INNER JOIN #Stay S ON
 S.ArrivalDate >= CM.MonthStart
 AND S.ArrivalDate < CM.NextMonth
 WHERE CM.MonthID BETWEEN @StartMonth And @StartMonth + 11
 UNION ALL
 -- This gets the transactions from the year after.
 SELECT CM.MonthName, S.TotalRevenue, 2
 , CM.MonthID - 12 - @StartMonth
 , RIGHT(CONVERT(VARCHAR, DATEPART(year, CM.MonthStart)), 2)
 FROM CalMonth CM
 INNER JOIN #Stay S ON
 S.ArrivalDate >= CM.MonthStart
 AND S.ArrivalDate < CM.NextMonth
 WHERE CM.MonthID BETWEEN @StartMonth + 12 And @StartMonth + 23
 ) AS X
GROUP BY X.MonthName, X.MonthOrd
ORDER BY X.MonthOrd;

The result set returned looks like:

FirstYearMonth Year1Revenue SecondYearMonth Year2Revenue MonthOrd
JUN-08         814652.29    JUN-09          815557.55    0
JUL-08         850882.41    JUL-09          831792.32    1
AUG-08         844237.14    AUG-09          837465.42    2
SEP-08         836915.91    SEP-09          833296.03    3
OCT-08         838332.34    OCT-09          855292.19    4
NOV-08         819412.58    NOV-09          827802.45    5
DEC-08         838095.67    DEC-09          851131.06    6
JAN-09         858064.48    JAN-09          0.00         7
FEB-09         771925.00    FEB-09          0.00         8
MAR-09         855450.64    MAR-09          0.00         9
APR-09         821391.97    APR-09          0.00         10
MAY-09         853181.60    MAY-09          0.00         11

The average CPU time was 360 ms - 10 times faster. The result set isn't quite the same, but serves the same purpose here. I find the code easier to read without all of the date calculations. Instead of DATEADD type calculations simple integer math is used since all months are sequential integers.

I find calendar tables make code much easier to read and maintain, mostly increase performance and they provide an easy way to change code (i.e. just change the table) when business rules change over time. After a few times using them, you can easily get hooked on them.


This article is part of the series Calendar Tables:

Total article views: 38176 | Views in the last 30 days: 123
 
Related Articles
FORUM

dateAdd inside where clause

dateAdd function syntax help

ARTICLE

Date Manipulation with DATEADD/DATEDIFF

Learn how to use Dateadd/Datediff functions to manipulate dates in this short article from Seth Phel...

FORUM

DATEADD with milliseconds

How determine last day/ last millisecond of month?

BLOG

DATEADD and variables in SQL

To make the T-SQL  function DATEADD more dynamic use variables. The example below , accepts an input...

FORUM

Dateadd

Hello, I am trying to accomplish a simple task. I want to display the dates (DATE) format in succ...

Tags
date manipulation    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones