1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
|
select /*+ NO_QUERY_TRANSFORMATION */ Main_Service_Number,
Time_Slot,
SUM(Direct_Entered_Call) as Tot_Direct_Entered_Call,
Call_Group
from
( select a.DNIS || ' (' || NVL(b.DINA, a.DNIS) || ')' as Main_Service_Number,
a.CATI as Time_Slot,
a.TCDQ as Direct_Entered_Call,
case
when a.ROPO IN (47000,47001,47009,47010,47011,47002,47014)
then '1st'
when a.ROPO IN (47015)
then 'AL'
when a.ROPO IN (47022,47025,47012)
then 'SUB'
when a.ROPO IN (40381,40384,40391,45115,45729,40382,40385,40392,45569,40383,40386,40393,45451,45612,45636,40388,40394,45111,45220,45219,40395)
then 'CM'
when a.ROPO IN (47016,47017,47018,45416,45415,44696,45216,45225,45266,45977)
then 'PA'
when a.ROPO IN (47003,47013,45457,45571,45458,45459,45460)
then 'DM'
when a.ROPO IN (47020,47021,45973,45974,45975,47024)
then 'PET'
else NULL
end as Call_Group
from CTMP.HDIGRE@CTMP_R a
INNER JOIN CTMP.DNISMA@CTMP_R b ON a.DNIS = b.DNIS
where a.CADA = date '2015-10-25'
and a.CATI Between '09' And '20'
and a.ROPO IN (47000,47001,47009,47010,47011,47002,47014,47015,47022,47025,47012,40381,40384,40391,45115,45729,40382,40385,40392,45569,40383,40386,40393,45451,45612,45636,40388,40394,45111,45220,45219,40395,47016,47017,47018,45416,45415,44696,45216,45225,45266,45977,47003,47013,45457,45571,45458,45459,45460,47020,47021,45973,45974,45975,47024)
)
group by Main_Service_Number, Time_Slot, Call_Group |
Partager