SQL Server Query monthly totals

I have a query that totals up all cases that were active on 01/01/2010.

SELECT     COUNT(CaseID) AS Total
FROM         dbo.ClientCase
WHERE     (CaseStartDate <= CONVERT(DATETIME, '2010-01-01 00:00:00', 102)) AND (CaseClosedDate >= CONVERT(DATETIME, '2010-01-01 00:00:00', 102)) OR (CaseClosedDate IS NULL)
GROUP BY CaseStartDate

I also have a Calendar table that I can use to create a query that returns 12 dates starting from 1st Jan, 1st Feb, 1st Mar thru to 1st Dec.

I can't work out how to combine the 2 queries so I get a count of all of the Case totals for the 1st of each month.

I need to see something like

Month   Total
Jan     102
Feb     130
Mar     145
.....
Dec     162
7
задан marc_s 26 September 2010 в 09:25
поделиться