Query to show SLA violated and not violated request count based on technician (MSSQL)

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 violated",
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "SLA not violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN workorder_queue ON workorder_queue.workorderid=wo.workorderid
LEFT JOIN queuedefinition ON workorder_queue.queueid=queuedefinition.queueid
WHERE (wo.ISPARENT='1')  and
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,'2017-03-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,'2018-03-01 23:59',21)
  GROUP BY ti.FIRST_NAME 
order by 1

                  New to ADSelfService Plus?