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)))

AND

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.

Regards

Lee