Da probam jos jednom.
Ovo je originalni upit (sa malim izmenama radi zastite podataka)
Code:
with sup as (
SELECT 'AA1234' as user_id, 'A*** A***' as user_name, 'RP1234' as supervisor_id, 'AAAA' as sup_name, 'BBBB' as hub, 'CCCC' as role from dual union all
SELECT 'AS5678' as user_id, 'A*** S***' as user_name, 'EC1234' as supervisor_id, 'EEEEE as sup_name, 'CCCC' as hub, 'CCCC' as role from dual union all
SELECT 'AH1234' as user_id, 'A**** H****' as user_name, 'None' as supervisor_id, 'Inactive Employee' as sup_name, 'DDDDD' as hub, 'None' as role from dual union all
....
)
, dt as (
select decode('&start', 'mdy', trunc(sysdate) - 3, 'd', trunc(sysdate) - 1,
'w', next_day( trunc(sysdate) - interval '14' day, 'SUN'), 'm', trunc(trunc(sysdate, 'MM')-1, 'MM')
, 'mdya', (trunc(sysdate) - 3)-(4/24), 'da', (trunc(sysdate) - 1) - (4/24), 'wa', next_day( trunc(sysdate) - interval '14' day, 'SUN')
, 'mapac', trunc(trunc(sysdate, 'MM')-1, 'MM') - (4/24)) as start_dt
, decode('&end', 'mdy', trunc(sysdate) - 2, 'd', trunc(sysdate),
'w', next_day( trunc(sysdate) - interval '7' day, 'SUN'), 'm', trunc(sysdate, 'MM')
, 'mdya', trunc(sysdate) - 2, 'da', trunc(sysdate)-(16/24), 'wa', next_day( trunc(sysdate) - interval '7' day, 'SUN')
, 'ma', trunc(sysdate, 'MM')-(16/24)) as end_dt from dual
, prd as (
select /*+materialize*/ a.p38, a.case_id, a.case_internal_id, a.p20, ad.create_date
, decode(ad.event, 'Assigned', trim(upper(replace(ad.comments, 'Case Owner Updated to ', ''))), trim(upper(substr(u.login_name, 1, 7)))) as soeid
, ad.event
, replace(replace(REGEXP_SUBSTR(ad.comments, '((Step changed|Status changed) from.......) as stepfrom
, replace(replace(REGEXP_SUBSTR(ad.comments, '((Step changed|Status changed) from.......) as stepto
from amlrcm.cases a
inner join amlrcm.ACM_MD_CASE_TYPES t on t.case_type_internal_id = a.case_type_internal_id
inner join amlrcm.ACM_MD_BUSINESS_UNITS bu on bu.bu_internal_id = a.bu_internal_id
inner join amlrcm.acm_case_audits aa on aa.case_internal_id = a.case_internal_id
inner join amlrcm.acm_audits ad on ad.audit_internal_id = aa.audit_internal_id
left outer join amlrcm.acm_users u on u.user_internal_id = ad.user_internal_id
where t.CASE_TYPE_IDENTIFIER like 'ROE L1%' and bu.bu_identifier <> 'ROE-GNRL' and ad.event in ('Status changed', 'Assigned')
and ad.create_date >= (select start_dt from dt)
and ad.create_date < (select end_dt from dt)
)
select sup.user_name, sup.user_id, sup.hub, sup.role, sup.sup_name, (select start_dt from dt) as strt_dt, (select end_dt from dt) as end_dt
, nvl(L1, 0) as L1
, nvl(L2, 0) as L2
, nvl(L3, 0) as L3
, nvl(L4, 0) as L4
, nvl(L5, 0) as L5
, nvl(L6, 0) as L6
, nvl(L7, 0) as L7
, nvl(L8, 0) as L8
, nvl(L9, 0) as L9
, nvl(L10, 0) as L10
, nvl(L11, 0) as L11
from sup
left outer join (
select case when stepfrom like '%Closed%' and stepto like '%Match%' then asgn_soeid else soeid end as soeid
, sum(decode(p20, 'Ent', decode(stepto, 'L1 - Closed', 1, 0), 0)) as L1
, sum(decode(p20, 'Ind', decode(stepto, 'L1 - Closed', 1, 0), 0)) as L2
, sum(decode(trim(p20), NULL, decode(stepto, 'L1 - Closed', 1, 0), 0)) as L3
, sum(decode(stepto, 'L1 - Closed', 1, 0)) as L4
, sum(decode(p20, 'EnT', decode(stepto, 'L1 Sub-Case - Closed', 1, 0), 0)) as L5
, sum(decode(p20, 'Indl', decode(stepto, 'L1 Sub-Case - Closed', 1, 0), 0)) as L6
, sum(decode(trim(p20), NULL, decode(stepto, 'L1 Sub-Case - Closed', 1, 0), 0)) as L7
, sum(decode(stepto, 'L1 Sub-Case - Closed', 1, 0)) as L8
, sum(case when stepfrom like 'L1 - Closed%' and stepto like 'L1 - Match%' then 1 else 0 end) as L9
, sum(case when stepfrom like 'L1 Sub-Case - Closed%' and stepto like 'L1 Sub-Case - MatcH%' then 1 else 0 end) as L10
from (
select prd.case_id, prd.p20, prd.event, prd.create_date, prd.stepfrom, prd.stepto, prd.soeid
, min(asgn.create_date) as asgn_dt, nvl(substr(min(to_char(asgn.create_date, 'yyyy-mm-dd HH24:mi:ss')||asgn.soeid), 20), '1') as asgn_soeid
from prd
left outer join prd asgn on asgn.case_id = prd.case_id and asgn.event = 'Assigned' and asgn.create_date > prd.create_date
where prd.event = 'Status changed'
group by prd.alert_id, prd.p20, prd.event, prd.create_date, prd.stepfrom, prd.stepto, prd.soeid
) d
group by case when stepfrom like '%Closed%' and stepto like '%Match%' then asgn_soeid else soeid end
) p on p.soeid = sup.user_id
left outer join (
select count(distinct ea.entity_id) as match_cnt, trim(upper(substr(u.login_name, 1, 7))) as soeid, u.full_name
from cmt_extn.ROE_ENT_AUDITS ea
inner join amlrcm.acm_audits ad on ad.audit_internal_id = ea.audit_internal_id
left outer join amlrcm.acm_users u on u.user_internal_id = ad.user_internal_id
where EA.ENTITY_TYPE = 'ROE_MATCh' and ad.event like 'Match Decision%'
and ad.create_date >= (select start_dt from dt)
and ad.create_date < (select end_dt from dt)
On mi trenutno daje broj uradjenih case-ova po analyst-u za vremenski period koji unesem na pocetku: dan, nedelja ili mesec.
E sad mi treba da izmenim da bi mi davao u nedeljnom i mesecnom izvestaju - ukupan broj radnih dana koji je svaki analyst imao, ukupan broj case-ova i prosek po danu.
Tajna izdrzljivosti Kineskog zida lezi u lepljivom pirinchu!