Aggregation against multiple date fields

alastair · #1 (**permalink**) 11-30-05, 10:50

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!

madafaka · #2 (**permalink**) 11-30-05, 11:38

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'

alastair · #3 (**permalink**) 11-30-05, 13:35

Thankyou so much for the helpful reply - you're a star!

Peter.Vanroose · #4 (**permalink**) 12-03-05, 15:59

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

)

Internet Services

Web Development

Digital Marketing

Consumer Tech