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?
- 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
- 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?
Related Articles
Query to show SLA response and resolution success percentage
Last Tested on builds 14500 Databases: PGSQL & MSSQL: SELECT pd.PRIORITYNAME AS "Priority" , 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 ...Query to show SLA violated and not violated request count based on technician (MSSQL)
Tested in Build MSSQL (14306) SELECT ti.FIRST_NAME "Technician", count(wo.WORKORDERID) "Total Request", count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Completed Requests" , count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "SLA ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show who actually violated the SLA (PGSQL & MSSQL )
Working on Builds 14500 and above. Tested in build PGSQL (14300) and MSSQL (14306) Databases: PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", max(wo.TITLE) "Subject", max(ti.FIRST_NAME) "Current Technician", max(std.STATUSNAME) "Request Status", ...Query to get complete SLA details. (PGSQL)
Tested in build PGSQL (14300) To get the complete list of SLA configured in the application. PGSQL: SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...