Request First Assigned time

Request First Assigned time

First Assigned Time is the number of minutes, hours, or days between when a customer submits a support ticket and when was a support representative assigned to the Request. It indicates how long it is in an 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' )
  AND wo.CREATEDTIME >= <from_lastmonth>
  AND wo.CREATEDTIME <= <to_lastmonth>
GROUP BY wo.WORKORDERID ORDER BY 1
 

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 




          • Related Articles

          • Time taken to First Response

            First Response Time is the number of minutes, hours, or days between when a customer submits a support ticket and when a customer support representative provides an initial response. It indicates how long a customer has to wait before being helped. ...
          • Time elapsed analysis

            This Report gives a clear picture of, how long the request was handled by each technician, stayed in each group and stayed in different statuses. Capturing each action performed on the request can be helpful for the request management team to assess ...
          • Project Time spent

            MSSQL SELECT pr.ProjectID "Project ID", pr.TITLE "Project Title", taskdet.TaskID "TaskID", taskdet.TITLE "Title", tkd.description "Task Description", taskowner.FIRST_NAME "Owner", dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ...
          • Problem time spent

            SELECT prob.PROBLEMID "Problem ID", prob.TITLE "Title", catadef.CATEGORYNAME "Category", LONGTODATE(prob.REPORTEDTIME) "Reported Date", ownaaa.FIRST_NAME "Technician", priodef.PRIORITYNAME "Priority", statdef.STATUSNAME "Status", ...
          • Archived and Active Request in a same report

            This Report is used to get the complete list of request both active and archived request in the same report.  To make any changes to a query, refer to the KB article below. ...