User Management - first assigned time of a request
This report returns the first assigned time of a request. When the request was submitted and when was a technician assigned to it. This helps us know how long it was in the unassigned state.
SELECT wo.WORKORDERID "Request ID",
Longtodate(max(wo.CREATEDTIME)) "Created Time",
longtodate(max(woh.OPERATIONTIME)) "Assigned time",
max(aau.FIRST_NAME) "Requester",
max(wo.TITLE) "Subject",
max(qd.QUEUENAME) "Group",
min(prev.first_name) "Technician",
max(ti.FIRST_NAME) "Current Technician",
max(pd.PRIORITYNAME) "Priority",
max(cd.CATEGORYNAME) "Category",
max(scd.NAME) "Subcategory",
max(std.STATUSNAME) "Request Status" 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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
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
LEFT JOIN workorderhistory woh ON wo.workorderid=woh.workorderid
LEFT JOIN workorderhistorydiff wohd ON woh.historyid=wohd.historyid
LEFT JOIN aaauser prev ON cast(cast(wohd.current_value AS varchar) AS int)=prev.user_id
WHERE (wo.ISPARENT='1')
AND wohd.columnname='ownerid'
AND woh.historyid=
(SELECT min(workorderhistory.historyid) FROM workorderhistory
LEFT JOIN workorderhistorydiff ON workorderhistory.historyid=workorderhistorydiff.historyid
WHERE workorderid=wo.workorderid
AND workorderhistorydiff.COLUMNNAME ='ownerid' )
GROUP BY wo.WORKORDERID ORDER BY 1
To make any changes to this query, refer to this post.
Click this link to navigate to the next report.