Answered by:
How to do a count of two different items in SQL and "Bucketize" them by Year/Month

First off, thanks for taking the time to read this – I’m stumped.
I have a requirement to chart the rate of incoming and outgoing problem reports. (Same chart) Charting isn’t hard as long as I can aggregate the data the way I need it – But that’s where the problem arises.
My table has SubmittedDate and ClosedDate as Timestamp. Not all Problem Reports are closed, so the count isn’t the same.
I can “Bucketize” either of these with no problem, but I can’t create a query that will provide me a count of both, for example…
Period Open Close
March 2009 20 10
April 2009 15 30
May 2009 123 55
June 2009 55 140
Of course in the above there would be 30 entries for various days in March, 45 for various days in April, etc. Remember, both SubmittedDate and ClosedDate are Timestamp data types, so I need to do a DateName on them.
Any help would be so appreciated – I’ve been bashing my head on this for a few days now, I even walked away from it for about a month, but now it’s due, so I got to get it working.
Ron...
Question
Answers

Here is one way you could do it:
with cte as ( select dateadd(m, DATEDIFF(m, 0, SubmittedDate), 0) as dt, 'Opened' as pr, COUNT(*) as tot from tbl group by dateadd(m, DATEDIFF(m, 0, SubmittedDate), 0) union select dateadd(m, DATEDIFF(m, 0, ClosedDate), 0) as dt, 'Closed', COUNT(*) as tot from tbl where ClosedDate is not null group by dateadd(m, DATEDIFF(m, 0, ClosedDate), 0) ) select dt, SUM(case pr when 'Opened' then tot else 0 end) as 'open', SUM(case pr when 'Closed' then tot else 0 END) as 'close' from cte group by dt
 Marked as answer by RonInOttawa Tuesday, August 25, 2009 1:48 PM
All replies

Here is one way you could do it:
with cte as ( select dateadd(m, DATEDIFF(m, 0, SubmittedDate), 0) as dt, 'Opened' as pr, COUNT(*) as tot from tbl group by dateadd(m, DATEDIFF(m, 0, SubmittedDate), 0) union select dateadd(m, DATEDIFF(m, 0, ClosedDate), 0) as dt, 'Closed', COUNT(*) as tot from tbl where ClosedDate is not null group by dateadd(m, DATEDIFF(m, 0, ClosedDate), 0) ) select dt, SUM(case pr when 'Opened' then tot else 0 end) as 'open', SUM(case pr when 'Closed' then tot else 0 END) as 'close' from cte group by dt
 Marked as answer by RonInOttawa Tuesday, August 25, 2009 1:48 PM

I would take a similar approach as previously posted; however, instead of issuesing three aggregations, I would create the two queries and join them together.
DECLARE @t TABLE( Case_Id INT, Open_Dt DATETIME, Closed_Dt DATETIME ); INSERT INTO @t VALUES (1,'20090823 09:00 AM',NULL); INSERT INTO @t VALUES (2,'20090823 11:00 AM','20090823 02:00 PM'); INSERT INTO @t VALUES (3,'20090723 01:00 PM','20090723 02:00 PM'); INSERT INTO @t VALUES (4,'20090615 10:00 AM','20090616 01:00 PM'); INSERT INTO @t VALUES (5,'20090921 09:00 AM','20091021 09:00 AM'); SELECT COALESCE(Open_Cases.MonthYear,Closed_Cases.MonthYear) AS MonthYear, COALESCE(Open_Cases.Open_Cnt,0) AS Open_Cnt, COALESCE(Closed_Cases.Closed_Cnt,0) AS Closed_Cnt FROM( SELECT DATENAME(MONTH,Open_Dt) + SPACE(1) + CONVERT(CHAR(4),YEAR(Open_Dt)) AS MonthYear, COUNT(*) AS Open_Cnt FROM @t GROUP BY DATENAME(MONTH,Open_Dt) + SPACE(1) + CONVERT(CHAR(4),YEAR(Open_Dt)) ) AS Open_Cases FULL OUTER JOIN( SELECT DATENAME(MONTH,Closed_Dt) + SPACE(1) + CONVERT(CHAR(4),YEAR(Closed_Dt)) AS MonthYear, COUNT(*) AS Closed_Cnt FROM @t GROUP BY DATENAME(MONTH,Closed_Dt) + SPACE(1) + CONVERT(CHAR(4),YEAR(Closed_Dt)) ) AS Closed_Cases ON Open_Cases.[MonthYear] = Closed_Cases.[MonthYear] WHERE COALESCE(Open_Cases.MonthYear,Closed_Cases.MonthYear) IS NOT NULL ORDER BY CONVERT(DATETIME,COALESCE(Open_Cases.MonthYear,Closed_Cases.MonthYear))
http://jahaines.blogspot.com/ Edited by Adam HainesModerator Tuesday, August 25, 2009 2:24 PM corrected code even further



Here is another possible solution. Here I use a "cross join" to split each row in two, one for "open" and another for "close".
select substring(convert(varchar(15), dateadd([month], datediff([month], '19000101', case S.c1 when 1 then Open_Dt else Closed_Dt end), '19000101'), 113), 4, 8) as period, sum(case when S.c1 = 1 then 1 else 0 end) as [open], sum(case when S.c1 = 2 then 1 else 0 end) as [close] from @t as T cross join (select 1 as c1 union all select 2) as S where case S.c1 when 1 then Open_Dt else Closed_Dt end is not null group by dateadd([month], datediff([month], '19000101', case S.c1 when 1 then Open_Dt else Closed_Dt end), '19000101') order by dateadd([month], datediff([month], '19000101', case S.c1 when 1 then Open_Dt else Closed_Dt end), '19000101'); GO
Thanks to Adam for providing us sample data.AMB


DECLARE @t TABLE ( Case_Id INT, Open_Dt DATETIME, Closed_Dt DATETIME ); INSERT INTO @t VALUES (1,'20090823 09:00 AM',NULL); INSERT INTO @t VALUES (2,'20090823 11:00 AM','20090823 02:00 PM'); INSERT INTO @t VALUES (3,'20090723 01:00 PM','20090723 02:00 PM'); INSERT INTO @t VALUES (4,'20090615 10:00 AM','20090616 01:00 PM'); select datename(month, mth) + ' ' + datename(year, mth), open_mth, close_mth from ( select type, mth from ( select open_mth = dateadd(month, datediff(month, 0, Open_Dt), 0), close_mth = dateadd(month, datediff(month, 0, Closed_Dt), 0) from @t ) d unpivot ( mth for type in (open_mth, close_mth) ) p ) d pivot ( count( type ) for type in ([open_mth], [close_mth]) ) p
KH Tan 

Adam,
Using an "inner join" will exclude opening without any closing in the same period. I guess "full join" could serve better.
INSERT
INTO @t VALUES (1,'20090921 09:00 AM',NULL);
AMB
Alejandro,
You are correct a full join is more appropriate here. Thanks for the catch. I will mod the code I posted.
http://jahaines.blogspot.com/ Edited by Adam HainesModerator Tuesday, August 25, 2009 2:15 PM syntax