Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    6

    Unanswered: Aggregation against multiple date fields

    I have a single table which records calls made to a helpdesk.
    Each call has a single row in the table, and there are three relevant date fields recorded: occurrence date, reported date, and resolution date.

    So it looks a little like this:

    Code:
    OccDate | RepDate| ResDate | CallRef
    21/3/05 |22/3/05 | 01/4/05 | PMR001
    22/3/05 | 1/4/05 | 26/5/05 | PMR002
    01/4/05 | 2/4/05 | 3/6/05  | PMR003
    01/5/05 | 2/5/05 |  <Null> | PMR004
    Now what I want to do is to create a single SQL statement which will allow me to summarise in each month how many calls occurred, were reported, and resolved, so for this data it looks like this:

    Code:
    Month  | Occ. | Rep. | Res.
    Mar-05 |   2  |  1   |  0
    Apr-05 |   1  |  2   |  1
    May-05 |   1  |  1   |  1
    Jun-05 |   0  |  0   |  1
    I can get each column individually very easily by simply doing:
    COUNT(CallRef) GROUP BY (OccDate)
    COUNT(CallRef) GROUP BY (RepDate)
    COUNT(CallRef) GROUP BY (ResDate)
    ..but I'm getting in a right pickle when trying to merge these into a single statement.

    To start with, I'm guessing that I need to do 2 self-joins so that all three dates are linked to each other.
    Then I tried using CASE and COALESCE statements to do conditional aggregations, but that got me nowhere...

    I'd be grateful for any help!
    Last edited by alastair; 11-30-05 at 11:52.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I tested it on MS SQL Server:
    Code:
    create table t (OccDate datetime, RepDate datetime, ResDate datetime, callRef varchar(10))
    
    insert into t values( cast('3/21/05' AS DATETIME) , cast('3/22/05' as DATETIME), cast('4/1/05' as datetime), 'PMR001')
    insert into t values( cast('3/22/05' AS DATETIME) , cast('4/1/05' as DATETIME), cast('5/26/05' as datetime), 'PMR002')
    insert into t values( cast('4/1/05' AS DATETIME) , cast('4/2/05' as DATETIME), cast('6/3/05' as datetime), 'PMR003')
    insert into t values( cast('5/1/05' AS DATETIME) , cast('5/2/05' as DATETIME), NULL, 'PMR004')
    
    select 
    	mont,
    	(select count(*) from t t3 where cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) = t2.mont) as Occ,
    	(select count(*) from t t3 where cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) = t2.mont) as Rep,
    	(select count(*) from t t3 where cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) = t2.mont) as Res
    from
    	(
    	 select distinct mon  as Mont from
    	 (
    	  select cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) as mon from t where OccDate is not null
    	  union all
    	  select cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) as mon from t where RepDate is not null
    	  union all
    	  select cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) as mon from t where ResDate is not null
    	 ) t1
            ) t2
    
    
    Month	        Occ	Rep	Res
    -----------------------------------------
    Apr-2005	1	2	1
    Jun-2005	0	0	1
    Mar-2005	2	1	0
    May-2005	1	1	1
    to run this on different DB server all you need is replace
    cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4))
    with other functions which returns
    'Month-Year'
    Last edited by madafaka; 11-30-05 at 12:42.

  3. #3
    Join Date
    Jun 2005
    Posts
    6
    Thankyou so much for the helpful reply - you're a star!

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    An other solution is the following:
    Code:
    create table t (OccDate date, RepDate date, ResDate date, callRef varchar(10)) ;
    insert into t values( '03/21/2005', '03/22/2005', '04/01/2005', 'PMR001') ;
    insert into t values( '03/22/2005', '04/01/2005', '05/26/2005', 'PMR002') ;
    insert into t values( '04/01/2005', '04/02/2005', '06/03/2005', 'PMR003') ;
    insert into t values( '05/01/2005', '05/02/2005', NULL, 'PMR004') ;
    SELECT coalesce(m1,m2,m3) AS "month",
           coalesce(Occ,0), coalesce(Rep,0), coalesce(Res,0)
    FROM   ( SELECT m1, COUNT(*) AS Occ
             FROM ( SELECT substr(char(OccDate,ISO),1,7) AS m1
                    FROM   t ) AS x1
             GROUP BY m1 ) AS y1
           FULL OUTER JOIN
           ( SELECT m2, COUNT(*) AS Rep
             FROM ( SELECT substr(char(RepDate,ISO),1,7) AS m2
                    FROM   t ) AS x2
             GROUP BY m2 ) AS y2
           ON m1 = m2
           FULL OUTER JOIN
           ( SELECT m3, COUNT(*) AS Res
             FROM ( SELECT substr(char(ResDate,ISO),1,7) AS m3
                    FROM   t ) AS x3
             GROUP BY m3 ) AS y3
           ON m2 = m3
    ORDER BY 1
    It has the slight advantage that it works with DB2 V7, and that it might be a bit faster (but I did not verify that )
    Last edited by Peter.Vanroose; 12-03-05 at 18:08.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •