Query to show response, resolution SLA violated count and its percentage

Query to show response, resolution SLA violated count and its percentage

DB: PGSQL & MSSQL:

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

      • Related Articles

      • Query to show SLA response and resolution success percentage

        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 float) else null end  "% Response ...
      • Query to show SLA violated and not violated request count based on technician

         Go to Reports-New Query Report and execute this query. 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 ...
      • Query to show who actually violated the SLA

        PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID",        max(wo.TITLE) "Subject",        max(ti.FIRST_NAME) "Current Technician",        max(std.STATUSNAME) "Request Status",        CASE            WHEN max(tech.first_name) IS NOT NULL THEN ...
      • Query to show SLA assigned to tickets

        PGSQL & MSSQL: SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" FROM WorkOrder wo LEFT JOIN WorkOrderStates ...
      • Query to show KPI report based on technician

        This report is already available under Frequently asked reports under Reports->New Query Reports page. Below query can be used for date filter and filter by technicians name modifications. SELECT ti.FIRST_NAME "Technician", count(wo.WORKORDERID) ...