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

    Question

  • 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...
    Tuesday, August 25, 2009 12:41 PM

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
    Tuesday, August 25, 2009 1:13 PM
    Moderator

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
    Tuesday, August 25, 2009 1:13 PM
    Moderator
  • 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,'2009-08-23 09:00 AM',NULL);
    INSERT INTO @t VALUES (2,'2009-08-23 11:00 AM','2009-08-23 02:00 PM');
    INSERT INTO @t VALUES (3,'2009-07-23 01:00 PM','2009-07-23 02:00 PM');
    INSERT INTO @t VALUES (4,'2009-06-15 10:00 AM','2009-06-16 01:00 PM');
    INSERT INTO @t VALUES (5,'2009-09-21 09:00 AM','2009-10-21 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/
    Tuesday, August 25, 2009 1:21 PM
    Moderator
  • In addition, my approach will only work in SQL2005 and above whereas Adams will be backward compatible across previous versions.


    every day is a school day
    Tuesday, August 25, 2009 1:34 PM
    Moderator
  • I went with yours as it seemed simpler to my feeble brain, We're running SQL Server 2005, so no prob there.

    This does EXACTLY what I've been struggling with for so very long.

    Thanks to both of you for your quick replies.

    Ron...
    Ron...
    Tuesday, August 25, 2009 1:49 PM
  • 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

    Tuesday, August 25, 2009 1:59 PM
    Moderator
  • 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,'2009-09-21 09:00 AM',NULL);


    AMB

    Tuesday, August 25, 2009 2:02 PM
    Moderator
  • DECLARE @t TABLE
    (
        Case_Id     INT,
        Open_Dt     DATETIME,
        Closed_Dt     DATETIME
    );
    
    INSERT INTO @t VALUES (1,'2009-08-23 09:00 AM',NULL);
    INSERT INTO @t VALUES (2,'2009-08-23 11:00 AM','2009-08-23 02:00 PM');
    INSERT INTO @t VALUES (3,'2009-07-23 01:00 PM','2009-07-23 02:00 PM');
    INSERT INTO @t VALUES (4,'2009-06-15 10:00 AM','2009-06-16 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
    Tuesday, August 25, 2009 2:07 PM
  • SELECT b.start_date, b.end_date,  COUNT(submitted_date) AS open_tickets_nbr, COUNT(closed_date) AS closed_tickets_nbr FROM table AS a
    INNER JOIN calendar AS b ON a.submitted_date BETWEEN b.start_date AND b.end_date
    GROUP BY b.start_date, b.end_date
    Tuesday, August 25, 2009 2:11 PM
  • 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,'2009-09-21 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/
    Tuesday, August 25, 2009 2:14 PM
    Moderator