Query to get SLA and escalation details in Custom View
Version: 11026
DB: PGSQL
OUTPUT:
select DISTINCT wo.workorderid "REQUEST ID",
max(wo.description) "Subject",
max(wo.title) "Title",
max(sladef.slaname) "SLA",
max(case when (slaesc.level='1') THEN
(select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 1 )
ELSE NULL END) "First Escalation",
max(case when (slaesc.level='2') THEN
(select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 2 ) ELSE NULL END) "2nd Escalation",
max(case when (slaesc.level='3') THEN (select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 3 ) ELSE NULL END) "3rd Escalation",
max(case when (slaesc.level='4') THEN (select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 4 ) ELSE NULL END) "4th Escalation" from workorder wo
LEFT join sladefinition sladef on wo.slaid=sladef.slaid
Inner JOIN requestslaescalations woslaesc on wo.workorderid = woslaesc.itemid
inner JOIN slaescalation slaesc on woslaesc.escalationid = slaesc.escalationid group by wo.workorderid
max(wo.description) "Subject",
max(wo.title) "Title",
max(sladef.slaname) "SLA",
max(case when (slaesc.level='1') THEN
(select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 1 )
ELSE NULL END) "First Escalation",
max(case when (slaesc.level='2') THEN
(select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 2 ) ELSE NULL END) "2nd Escalation",
max(case when (slaesc.level='3') THEN (select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 3 ) ELSE NULL END) "3rd Escalation",
max(case when (slaesc.level='4') THEN (select au.FIRST_NAME "name" from aaauser au
LEFT join SdUser on SdUser.userid=au.user_id
left JOIN escalateton escon1 on SdUser.userid = escon1.userid
LEFT JOIN escalatetomediator escmed1 on escmed1.escalatetoid = escon1.escalatetoid
inner JOIN slaescalation slaesc1 on slaesc1.escalatetoid = escmed1.escalatetoid
inner JOIN requestslaescalations woslaesc1 on woslaesc1.escalationid = slaesc1.escalationid
where woslaesc.itemid= woslaesc1.itemid and woslaesc1.escalationid = 4 ) ELSE NULL END) "4th Escalation" from workorder wo
LEFT join sladefinition sladef on wo.slaid=sladef.slaid
Inner JOIN requestslaescalations woslaesc on wo.workorderid = woslaesc.itemid
inner JOIN slaescalation slaesc on woslaesc.escalationid = slaesc.escalationid group by wo.workorderid