Report to show percentage of tickets answered before 1 hour

 

SELECT pd.PRIORITYNAME AS priority,(SUM(CASE WHEN (a.respondedtime-w.createdtime)/(3600000) <=’1′ THEN 1 ELSE 0 END)/COUNT(w.workorderid))*100 “Percentage of tickets answered before 1 hour” FROM workorder w

LEFT JOIN 

(SELECT wo.WORKORDERID AS requestid,MIN(woh.OPERATIONTIME) AS respondedtime 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 ‘%REPLY%’)

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

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