Query to show response, resolution SLA violated count and its percentage ( PGSQL & MSSQL)

Query to show response, resolution SLA violated count and its percentage ( PGSQL & MSSQL)

Tested in build PGSQL (14300) and MSSQL (14306)
SELECT pd.PRIORITYNAME AS "Priority" ,
count(wo.WORKORDERID) "Total Requests",
count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed",
count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END) "Response SLA violated",
count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) "Response SLA not violated" ,
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "Resolution SLA violated",
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "Resolution SLA not violated" ,
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Response SLA violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Response SLA not violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Resolution SLA Violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Resolution SLA not Violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> GROUP BY pd.PRIORITYNAME
order by 1

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>

More Fields

Sample Table



Query

SELECT wo.workorderid "Request ID",
aau.first_name "Requester",
wo.title "Subject",
cd.categoryname "Category",
pd.PRIORITYNAME AS "Priority",
count(wo.WORKORDERID) "Total Requests",
count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed",
count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END) "Response SLA violated",
count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) "Response SLA not violated" ,
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "Resolution SLA violated",
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "Resolution SLA not violated" ,
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Response SLA violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Response SLA not violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Resolution SLA Violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 as float)   / cast(count(wo.workorderid) as float) else null end  "% Resolution SLA not Violated",
ad.org_name "Account" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID" 
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID 
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> GROUP BY pd.PRIORITYNAME,wo.workorderid,aau.first_name,cd.categoryname,pd.PRIORITYNAME,wo.title,ad.org_name
order by 1

                  New to ADSelfService Plus?