SQL Server Query monthly totals

Question

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

Answer 1

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

Answer 2

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

Answer 3

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)