Query to show KPI report based on technician (PGSQL & MSSQL)

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 ti.FIRST_NAME "Technician",
count(wo.WORKORDERID) "Total number of calls created",
count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Total Closed calls" ,
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "No: of calls closed within SLA",
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "No: of calls closed exceeding SLA",
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  "% of calls created and completed within SLA",
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  "% of calls created and completed exceeding SLA"
 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 wo.CREATEDTIME >= DATETOLONG('2021-01-13 07:00:00')  AND wo.CREATEDTIME <= DATETOLONG('2021-09-13 07:00:00') AND ti.FIRST_NAME='New tech'
GROUP BY ti.FIRST_NAME 
order by 1

Note: Custom date and technician name is highlighted, please replace with the required value in the query.

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>

                  New to ADSelfService Plus?