Query for Average First Response Time Organized by Urgency and Technician per month
I have this query from a custom tabular report for the Average Overall Resolution Time organized by Technician and Urgency grouped by Months for this year and only in the Service Desk group:
SELECT ti.FIRST_NAME "Technician",urgdef.NAME "Urgency",wo.TIMESPENTONREQ "Time Elapsed",wo.CREATEDTIME "Created Time" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN UrgencyDefinition urgdef ON wos.URGENCYID=urgdef.URGENCYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE ((((qd.QUEUENAME = N'Service Desk' COLLATE SQL_Latin1_General_CP1_CI_AS) AND ((std.STATUSNAME = N'Closed' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME = N'Resolved' COLLATE SQL_Latin1_General_CP1_CI_AS))) AND (((wo.CREATEDTIME >= 1357016400000) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= 1388552399000) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND (((((((wo.SITEID = 13208) OR (wo.SITEID = 13204)) OR (wo.SITEID = 13206)) OR (wo.SITEID = 13207)) OR (wo.SITEID = 13205)) OR (wo.SITEID = 13203)) OR ((wos.OWNERID = 37508) OR (wo.REQUESTERID = 37508)))) AND wo.ISPARENT='1' ORDER BY 4
I need basically the same exact query except instead of the average Overall Resolution time, I need the average First Response Time (First Response Time minus Creation Time).
I also have this query report that gives me the Average First Response Time by Urgency for "last month" within the Service Desk. But I need it to sort by Technician and then Urgency, as I've stated above.
SELECT COUNT(wo.WORKORDERID) "Request Count ", urgdef.NAME "Urgency",
(SELECT CAST((AVG(ROUND(((wo1.RESPONDEDTIME-wo1.CREATEDTIME)/1000/3600),0)) + AVG(((wo1.RESPONDEDTIME-wo1.CREATEDTIME)/1000/60)%60)/60) AS VARCHAR(10)) + ':' + CAST(AVG(((wo1.RESPONDEDTIME-wo1.CREATEDTIME)/1000/60))%60 AS VARCHAR(10)) "Diff in Time"
FROM WorkOrder wo1
LEFT JOIN WorkOrderStates wos1 ON wo1.WORKORDERID=wos1.WORKORDERID
LEFT JOIN UrgencyDefinition urgdef1 ON wos1.URGENCYID=urgdef1.URGENCYID
LEFT JOIN WorkOrder_Queue woq ON woq.WORKORDERID = wo1.WORKORDERID
LEFT JOIN QueueDefinition qd ON qd.QUEUEID = woq.QUEUEID
WHERE (wo1.ISPARENT='1')
AND wo1.RESPONDEDTIME!=0
AND wo1.CREATEDTIME >= <from_lastmonth>
AND wo1.CREATEDTIME <= <to_lastmonth>
AND urgdef1.NAME =urgdef.NAME
AND qd.QUEUENAME IN ('Service Desk')
GROUP BY Qd.QUEUENAME,urgdef1.NAME) "Average"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN UrgencyDefinition urgdef ON urgdef.URGENCYID = wos.URGENCYID
LEFT JOIN WorkOrder_Queue woq ON woq.WORKORDERID = wo.WORKORDERID
LEFT JOIN QueueDefinition qd ON qd.QUEUEID = woq.QUEUEID
WHERE (wo.ISPARENT='1')
AND wo.CREATEDTIME >= <from_lastmonth>
AND wo.CREATEDTIME <= <to_lastmonth>
AND qd.QUEUENAME IN ('Service Desk')
GROUP BY Qd.QUEUENAME,urgdef.NAME