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 (CaseClosedDa开发者_如何学运维te >= 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

SELECT cal.MonthName, COUNT(CaseID) AS Total 
FROM dbo.calendarTable cal
LEFT OUTER JOIN dbo.ClientCase cc
ON Month(cal.MonthStartDate) = Month(CaseStartDate)
WHERE 
(CaseStartDate <= CONVERT(DATETIME, cal.MonthStartDate, 102)) AND 
(CaseClosedDate >= CONVERT(DATETIME, cal.MonthStartDate, 102)) OR 
(CaseClosedDate IS NULL) 
GROUP BY cal.MonthName

• Left outer join to ensure that you get all months including with 0 cases

Assuming Calendar has two columns such as MonthName and FirstDate, you want s/thing like

SELECT Calendar.MonthName AS Month, COUNT(ClientCase.CaseId) AS Total,
  FROM ClientCase
  JOIN Calendar
    ON (MONTH(Calendar.FirstDate) = MONTH(ClientCase.CaseStartDate))
  GROUP BY Calendar.MonthName

You could just simply group by the month of the query:

SELECT
    MONTH(CaseStartDate), 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 
    MONTH(CaseStartDate)

That should give you an output that's quite close (numerical months instead of Jan, Feb etc. - but close enough).

With this additional step, you'll get the first three letter's of each month's name:

SELECT
    SUBSTRING(DATENAME(MONTH, CaseStartDate), 1, 3) AS 'Month', 
    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 
    MONTH(CaseStartDate), SUBSTRING(DATENAME(MONTH, CaseStartDate), 1, 3)
ORDER BY
    MONTH(CaseStartDate)