SQL counting monthly totals from previous years data

Hi all

I am tying myself in knots with this one, so any help offered would be greatly appreciated.

I have an MSSQL database table that holds customer problem reports. Among the many fields in the tblproblems table are raisedDate and closureDate.

I need to write an SQL statement or stored procedure that trends (monthly) the number of open problem reports, over the past year.

Ideally this would be using the AVG function in some way to return the mean number of open problems for each of the preceding 12 months but I have no idea where to start with this.

As a compromise I have decided to take a snapshot of each month by looking at how many were open on the last day of each month. I have got this partially working, as an example this query returns the total open problem reports for the month of August 2009 (12 months ago at the time of writing) But I don’t know how to now iterate through the other 11 months of the year in the one statement.

SELECT Count(*), CONVERT(VARCHAR(24),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-12,0)),106) AS sampleDate
FROM tblproblems
WHERE raisedDate <= (DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-12,0)))
closureDate > (DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-12,0)))

Any ideas on the best way to achieve either of these two outcomes would be greatly appreciated.


I have managed to solve this one now (I think) and for completeness in case someone else has a similar problem here is the solution…

I adopted the auxiliary calendar table method described in this article…


and then wrote this SQL statement…

SELECT TheYear, TheMonth, AVG(Probs) As MeanOpen 
SELECT c.Y AS TheYear, c.M AS TheMonth, c.D AS TheDay,
COUNT(*) AS Probs
FROM dbo.tblproblemreports AS p
INNER JOIN dbo.Calendar AS c
ON c.dt >= p.datereceived
AND (c.dt <= p.closuredate OR closuredate IS NULL)
WHERE dt >= '20090801' AND dt <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
GROUP BY c.Y, c.M, c.D) AS subproblist
GROUP BY TheYear, TheMonth

Hope it might help somebody else one day.