Hi all;
We are trying to learn and leverage the "new" summary/grouping SQL
syntax in our environment (DB2 ESE V8 SP2 Win2000). Here's and example
of a typical query:
----------------------------------------------------------
With temp(dmMonthNum,dmYearNum,dmDayNum,agtnamelfm,HrCnt,TransactionDurationMin,
as
(
SELECT dmMonthNum,dmYearNum,dmDayNum,agtnamelfm,
Count(distinct dmhourdesc),
cast(sum(TrxD.trxdetaildursec)/60 as REAL),
count (*)
FROM phazzard.fct_trxdetail trxD
inner JOIN phazzard.dmn_transtype tt ON tt.transtypekeyid =
trxD.transtypekeyid
inner join phazzard.dmn_facility f on TrxD.facilitykeyid =
f.facilitykeyid
inner JOIN phazzard.dmn_location L on L.LocationKeyID =
TrxD.LocationKeyID
inner JOIN phazzard.dmn_inclination I on I.inclinationkeyid =
TrxD.inclinationkeyid
inner join phazzard.dmn_disposition d on d.dispositionkeyid =
TrxD.dispositionkeyid
inner join phazzard.dmn_datetime dt on dt.timekeyid = TrxD.timekeyid
inner join phazzard.dmn_agent A on TrxD.agtcontactkeyid =
A.agtcontactkeyid
where
transtypedesc = 'Triage Call'
group by (dmMonthNum,dmYearNum,dmDayNum,agtnamelfm)
)
Select dmMonthNum,dmYearNum,agtnamelfm, sum(HrCnt) as TotalHours,
sum(TransactionDurationMin)/sum(cnt) as AveCallDuration, sum(cnt) as
CallCount,
cast(sum(cnt) as real)/cast(sum(HrCnt) as real) as CallsPerHour
from temp
Group by (dmMonthNum,dmYearNum,agtnamelfm)
order by agtnamelfm
---------------------------------------------------
This accesses a star schema and produces a very powerful and flexible
report. Is there a way using cube/rollup/grouping sets to replace the
temp table? The temp table gets data at a daily level and the second
query "rolls up" and calculates at a monthly level. Any input is very
much appreciated. Thanks!
Pete H