Report to show percentage of tickets resolved without escalation or transfer

SELECT pd.PRIORITYNAME AS priority,(SUM(CASE WHEN (a.resolvedtime-w.createdtime)/(3600000) <=’8′ THEN 1 ELSE 0 END)/COUNT(w.workorderid))*100 “Percentage of tickets closed/resolved before 8 hours” FROM workorder w
LEFT JOIN 
(SELECT wo.WORKORDERID AS requestid,MAX(woh.OPERATIONTIME) AS resolvedtime FROM workorder wo
LEFT JOIN workorderhistory woh ON woh.WORKORDERID=wo.WORKORDERID
LEFT JOIN workorderhistorydiff wohd ON wohd.historyid=woh.HISTORYID
WHERE (woh.OPERATION LIKE ‘%resolve%’ OR woh.OPERATION LIKE ‘%close%’ ) GROUP BY wo.WORKORDERID) a ON a.requestid=w.workorderid
INNER JOIN workorderstates wos ON wos.WORKORDERID=w.workorderid
LEFT JOIN prioritydefinition pd ON pd.PRIORITYID=wos.PRIORITYID WHERE wos.ISOVERDUE = ‘false’
GROUP BY pd.PRIORITYNAME 
Works in both POSTGRES and MSSQL
This entry was posted in Reports.

Need More Help?

Are you looking for a personalized customer support? Submit your question or Call our Toll Free Number to speak with ServiceDesk Plus MSP Expert who can offer a better solution for your requirements.

Submit a Ticket
Email : sdpmsp@manageengine.com
Toll Free : 1-888-720-9500