User Management - report on time taken by technician to send a first response to a request
This report returns the time taken by technician to send a first response. It indicates how long a user had to wait to receive a response.
MSSQL:
SELECT wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
LONGTODATE(wo.CREATEDTIME) "Created Time",
LONGTODATE(wo.RESPONDEDTIME) "Responded Date",
convert(varchar(10), ((wo.RESPONDEDTIME)-(wo.createdtime))/1000/3600)+':'+convert(varchar(10), (((wo.RESPONDEDTIME)-(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),((((wo.RESPONDEDTIME)-(wo.createdtime)))/1000%60)) "Created Date - Responded Date" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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
WHERE (wo.ISPARENT='1')
AND RESPONDEDTIME !='0'
AND wo.CREATEDTIME >= <from_thisweek>
AND wo.CREATEDTIME <= <to_thisweek>
PGSQL:
SELECT wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
LONGTODATE(wo.CREATEDTIME) "Created Time",
LONGTODATE(wo.RESPONDEDTIME) "Responded Date",
TO_CHAR(((wo.respondedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') "Created Date - Responded Date" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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
WHERE (wo.ISPARENT='1')
AND RESPONDEDTIME !='0'
AND wo.CREATEDTIME >= <from_thisweek>
AND wo.CREATEDTIME <= <to_thisweek>
Click this link to navigate to the next report.