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 ...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 Executor Tool for PostGres
This tool is designed to execute queries in the customer environment by connecting the database by reading the database configuration file. We need to enter the query that we require to execute in queryToExecute.txt file. We can enter multiple ...Set Task Owner depending on the request field values
Requirement: Set Task Owner depending on the request field values. Usecase: When a task is triggered from a request, it should be assigned to a technician depending on the request field values. Steps to follow: Goto Admin > Task Custom Functions > ...Dynamic checklist based on field values
Requirement: Based on the answers provided for resource questions / request additional fields, dynamically a checklist has to be associated to the request UseCase: When an alarm is received indicating an issue with a server’s performance, with the ...