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
Dan Jansson's Developer Blog 