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

Could you indicate what you are trying to accomplish with this query? My first thought is that you may not need the Calendar table and instead could use SQL date functions (MONTH, YEAR, etc.). — Jeff Ogata
– Jeff Ogata, Commented Sep 26, 2010 at 1:18
@adrift - I have a variety of queries that work out how many cases were open at the beginning of a month, how many cases closed during a month and how many were created during a month. If you look at my first example above which totals open cases, it does what I want but only for Jan 1 2010. I need the dates in the first query to iterate thru the other 11 months of the year. — Mitch
– Mitch, Commented Sep 27, 2010 at 18:49

Jagmag · Accepted Answer · 2010-09-26 01:18:43Z

5

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

answered Sep 26, 2010 at 1:18

Jagmag

10.4k1 gold badge37 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mitch Over a year ago

Your answer was the closest what I needed so many thanks for your help.

Alex Martelli · Accepted Answer · 2010-09-26 01:11:07Z

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

answered Sep 26, 2010 at 1:11

Alex Martelli

887k175 gold badges1.3k silver badges1.4k bronze badges

Comments

marc_s · Accepted Answer · 2010-09-26 09:33:09Z

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)

Collectives™ on Stack Overflow

SQL Server Query monthly totals

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related