Query to show technician total tickets and SLA violated percentage. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Go to Reports- New Query Report and execute this query.
select aaau.first_name "Tech Name",
count (wo.workorderid) "Total No. of Requests/Incidents",
count(case when wos.ISOVERDUE='1' THEN 1 ELSE NULL END) "Total Overdue Tickets",
sum(CASE WHEN wos.ISOVERDUE = '1' THEN 1 ELSE 0 END) * 100 / Count(wo.workorderid) AS "Violation %" from workorder wo
left join workorderstates wos ON wo.workorderid=wos.workorderid
left join aaauser aaau ON wos.ownerid=aaau.user_id
WHERE (wo.ISPARENT='1') and wo.CREATEDTIME >= <from_lastmonth> AND wo.CREATEDTIME <= <to_lastmonth>
group by aaau.first_name
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 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 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 assigned to tickets
Working on Builds: 14500 and above Database: 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" ...Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...