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?
Related Articles
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 ...Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...Query to show KPI report based on technician (PGSQL & MSSQL)
Working on Builds: 14500 Database: MSSQL, PGSQL 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 ...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 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 ...