How to expose data for each month in an intervall

I recently stumbled into a problem where I was supposed to display aggregated data based on a time-period for 15 months.

Ok, so this seems to be a trivial thing, right? It is just to write a query such as:

SELECT  CAST(DATEPART(year, Date) AS CHAR(4)) 
          + REPLICATE('0', 2 - LEN(CAST(DATEPART(month, Date) AS CHAR(2))))
          + CAST(DATEPART(month, Date) AS CHAR(2)) MeetingDate,
                COUNT(Date) Occasions
FROM TestData
GROUP BY CAST(DATEPART(year, Date) AS CHAR(4)) 
          + REPLICATE('0', 2 - LEN(CAST(DATEPART(month, Date) AS CHAR(2))))
          + CAST(DATEPART(month, Date) AS CHAR(2))
ORDER BY 1

This query yields in the following result in my scenario:

MeetingDate Occasions 
-------------------------------- ----------- 
200709 1 
200711 1 
200712 1 
200803 1 
200805 1 
200806 3 
200807 1 
200808 3 
200810 2 
200811 1 

(10 row(s) affected)

Well, it is not that simple because there might be gaps in the data, i.e. some months have no records, so the results above displays September, November, December, but not October, which is not our desired result since we want to expose all months between the first and the last occasion even if there is no data for the current month.

So, how do we write a query that gives us this result?

I came across the concept of an auxilliary table of numbers that I read about in Itzik Ben-Gan’s book "SQL Server – Querying". Itzik tells that by using an auxilliary table of numbers it is possible to easily get a numbered sequence that can be used to create a resultset which can be very handy if you don’t have a logical sequence.

After a little brainstorming I came up with the following solution which solved my problem.

First som preparations needed for our example

USE Master 
GO 
CREATE DATABASE Test 
GO 
USE Test 
GO 

Create the table that will contain the numbered sequence, i.e. the auxilliary table of numbers.

This table will only contain numbers from 1 to n.

CREATE TABLE dbo.Nums (Num int) 
GO

Populate the dbo.Nums table with the numbers needed. I only needed a small amount of numbers for my solution, but

if you need more numbers, just add a larger number below or Google for "Auxilliary table of numbers" and "Itzik Ben-Gan".

DECLARE @CurrentNumber int 
SET @CurrentNumber = 0 
WHILE @CurrentNumber < 50 
BEGIN 
SELECT @CurrentNumber = @CurrentNumber + 1 
INSERT INTO dbo.Nums VALUES (@CurrentNumber) 
END 
GO 

Create some test-data for this example

CREATE TABLE TestData ( 
ID int identity not null, 
Date datetime not null 
) 
GO 
INSERT INTO TestData VALUES (dateadd(month, -1, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -2, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -2, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -4, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -4, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -4, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -5, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -6, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -6, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -6, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -7, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -9, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -12, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -13, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -15, getdate())) 
GO

Get the earliest and latest date from the testdata; these dates will be used to get the number of months for the time-period.

DECLARE @MinDate datetime 
SET @MinDate = (SELECT MIN(Date) FROM testdata) 
DECLARE @MaxDate datetime 
SET @MaxDate = (SELECT MAX(Date) FROM testdata)

Get the number of months between the first and last date.

DECLARE @NumberOfMonths int 
SET @NumberOfMonths = (SELECT DATEDIFF(MONTH, @MinDate, @MaxDate))

Create a table variable to contain the intermediate result.

DECLARE @TempData TABLE ( 
DateTemp varchar(6), 
Occasions int, 
MonthNo int)

Populate the table-variable with the correct number of rows that corresponds to the @NumberOfMonths-variable.

INSERT INTO @TempData 
SELECT CAST(DATEPART(year, DATEADD(MONTH, Num -1, @MinDate)) AS CHAR(4)) 
+ REPLICATE('0', 2 - LEN(CAST(DATEPART(month, DATEADD(MONTH, Num - 1, @MinDate)) AS CHAR(2)))) 
+ CAST(DATEPART(month, DATEADD(MONTH, Num - 1, @MinDate)) AS CHAR(2)), 
0, 
Num 
FROM dbo.Nums 
WHERE Num <= @NumberOfMonths

Update the table-variable with correct data.

UPDATE @TempData 
SET 
Occasions = Occasions + ar.NumberOfOccasions 
FROM ( 
SELECT 
COUNT(Date) NumberOfOccasions, 
DATEDIFF(month, @MinDate, Date) + 1 MonthName 
FROM TestData 
GROUP BY DATEDIFF(month, @MinDate, Date) + 1 
) ar 
WHERE MonthNo = ar.MonthName

Display the correct result from the table variable.

SELECT * FROM @TempData 
ORDER BY MonthNo

Here is the desired result that we wanted

DateTemp Occasions MonthNo 
-------- ----------- ----------- 
200709 1 1 
200710 0 2 
200711 1 3 
200712 1 4 
200801 0 5 
200802 0 6 
200803 1 7 
200804 0 8 
200805 1 9 
200806 3 10 
200807 1 11 
200808 3 12 
200809 0 13 
200810 2 14

(14 row(s) affected)

Clean up and delete the demo database.

USE Master 
GO 
DROP DATABASE Test 
GO
Advertisements

Hello world!

So, finally I got myself a technical blog where I can write down my thoughts and discoveries in the magic world of development. I have been thinking about getting this blog for a really long time now, but I havent really had the time (or havent been smart enough) to do it until now.

I hope you will find some of my posts useful and I’m looking forward to hear from you.

Cheers!
   Dan