Query on displaying columns in percentage values
PGSQL:
SELECT aau.first_name AS "Technician",
Count(wo.workorderid) AS "Total No. of Tickets Created",
Sum(CASE
WHEN std.internalname LIKE 'closed' THEN 1
ELSE 0
END) AS "Total No. of Tickets Closed",
Sum(CASE
WHEN std.internalname LIKE 'closed' THEN 1
ELSE 0
END) * 100 / Count(wo.workorderid) AS "% Tickets Completed",
Sum(CASE
WHEN std.internalname LIKE 'onhold' THEN 1
ELSE 0
END) AS "Total No. of Onhold Tickets",
Sum(CASE
WHEN std.internalname LIKE 'onhold' THEN 1
ELSE 0
END) * 100 / Count(wo.workorderid) AS "% of OnHold Tickets",
Sum(CASE
WHEN wos.reopened = 't' THEN 1
ELSE 0
END) * 100 / Count(wo.workorderid) AS "% of ReOpened Tickets",
Sum(CASE
WHEN std.internalname LIKE 'closed'
AND wos.isoverdue = 'f' THEN 1
ELSE 0
END) AS
"Total closed tickets within SLA",
Sum(CASE
WHEN std.internalname LIKE 'closed'
AND wos.isoverdue = 't' THEN 1
ELSE 0
END) AS
"Total ticket closed breaching SLA",
CASE
WHEN Sum(CASE
WHEN std.internalname LIKE 'closed' THEN 1
ELSE 0
END) > 0 THEN Sum(CASE
WHEN std.internalname LIKE 'closed'
AND wos.isoverdue = 'f' THEN 1
ELSE 0
END) * 100 / Sum(CASE
WHEN std.internalname
LIKE
'closed' THEN 1
ELSE 0
END)
ELSE 0
END AS
"% of total completed tickets within SLA",
CASE
WHEN Sum(CASE
WHEN std.internalname LIKE 'closed' THEN 1
ELSE 0
END) > 0 THEN Sum(CASE
WHEN std.internalname LIKE 'closed'
AND wos.isoverdue = 't' THEN 1
ELSE 0
END) * 100 / Sum(CASE
WHEN std.internalname
LIKE
'closed' THEN 1
ELSE 0
END)
ELSE 0
END AS
"% of total completed tickets breaching SLA",
to_char((Avg(wo.timespentonreq)/1000 || 'second' )::interval,'HH24:MI:SS') AS "Average Time Elapsed",
Sum(CASE
WHEN wos.isescalated = 't' THEN 1
ELSE 0
END) * 100 / Count(wo.workorderid) AS "% of tickets Escalated" FROM workorder wo
LEFT JOIN workorderstates wos
ON wo.workorderid = wos.workorderid
LEFT JOIN sduser sd
ON wos.ownerid = sd.userid
LEFT JOIN aaauser aau
ON sd.userid = aau.user_id
LEFT JOIN statusdefinition std
ON wos.statusid = std.statusid
WHERE wo.isparent='1'
GROUP BY 1;
New to ADSelfService Plus?
Related Articles
Additional field values
This report helps to find the picklist values in the additional fields. First you need to find the name of the table and the column holding the data, by executing the following SDP query report: SELECT Tablename, Columnname FROM ColumnAliases WHERE ...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 ...1. Query Basics
Basic Query: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON ...Query to retrieve the Change Details
Tested in: 14504, 14610 QUERY: SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS ...Query to retrieve response time in minutes
To return the ticket response time in minutes along with other request details. TESTED IN: Builds 14700 (Postgres) QUERY: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...