breakdown of technician,

How to find the breakdown time of each technician assignment in request?

Eg: If you have multiple request tickets, each ticket has been assigned to multi-technician ( the first assignment to tech A, then assign to tech B, then assign to tech C, etc). You want to know measure exact time of each assignment for each technician (from receiving assignment until assign to other technician or ticket resolved).

Please use the below query to achieve the above requirements

PGSQL Query

Status

SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",case when wos.ISOVERDUE='1' then 'Yes' else 'No' end "Overdue Status",
sd1.statusname "From status",
sd2.statusname "To status",
(wsi.TIMESPENT/60)||':'||((wsi.TIMESPENT))%60 "Time Spent (hrs)" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join wo_status_info wsi on woa.assessmentid=wsi.assessmentid
left join statusdefinition sd1 on wsi.statusid=sd1.statusid
left join statusdefinition sd2 on wsi.nextstatusid=sd2.statusid  WHERE (wo.ISPARENT='1')

Technician

SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",case when wos.ISOVERDUE='1' then 'Yes' else 'No' end "Overdue Status",
ti1.FIRST_NAME "From technician",
ti2.FIRST_NAME "To technician",
(wti.TIMESPENT/60)||':'||((wti.TIMESPENT))%60 "Time Spent (hrs)" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_TECH_INFO wti on woa.assessmentid=wti.assessmentid
LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID
LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID
 WHERE (wo.ISPARENT='1')


Group

SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",case when wos.ISOVERDUE='1' then 'Yes' else 'No' end "Overdue Status",
qd1.QUEUENAME  "From Group",
qd2.QUEUENAME  "To Group",
(wog.TIMESPENT/60)||':'||((wog.TIMESPENT))%60 "Time Spent (hrs)" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_GROUP_INFO wog on woa.assessmentid=wog.assessmentid
LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON wog.nextgroupid=qd2.QUEUEID
 WHERE (wo.ISPARENT='1')


MSSQL Query



1.Status
SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",case when wos.ISOVERDUE='1' then 'Yes' else 'No' end "Overdue Status",
sd1.statusname "From status",
sd2.statusname "To status",
concat((wsi.TIMESPENT/60),':',((wsi.TIMESPENT))%60) "Time Spent (hrs)" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join wo_status_info wsi on woa.assessmentid=wsi.assessmentid
left join statusdefinition sd1 on wsi.statusid=sd1.statusid
left join statusdefinition sd2 on wsi.nextstatusid=sd2.statusid  WHERE (wo.ISPARENT='1')

2.Technician
SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",case when wos.ISOVERDUE='1' then 'Yes' else 'No' end "Overdue Status",
ti1.FIRST_NAME "From technician",
ti2.FIRST_NAME "To technician",
concat((wti.TIMESPENT/60),':',((wti.TIMESPENT))%60) "Time Spent (hrs)" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_TECH_INFO wti on woa.assessmentid=wti.assessmentid
LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID
LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID
 WHERE (wo.ISPARENT='1')

3.Group

SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",case when wos.ISOVERDUE='1' then 'Yes' else 'No' end "Overdue Status",
qd1.QUEUENAME  "From Group",
qd2.QUEUENAME  "To Group",
concat((wog.TIMESPENT/60),':',((wog.TIMESPENT))%60) "Time Spent (hrs)" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_GROUP_INFO wog on woa.assessmentid=wog.assessmentid
LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON wog.nextgroupid=qd2.QUEUEID
 WHERE (wo.ISPARENT='1')
        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • Steps to import Archived Request into Analytics Plus

                        By default, we don't import archived request details into Analytics Plus but we are working on a model to import these details out of the box and will be available in one of our future builds. Meanwhile, please follow the below text instructions Step ...

                      • technician groups

                        If you want technician groups, then, use the below query in analytics plus  select qd.QUEUENAME "Support group", au.FIRST_NAME "Technician" from QueueDefinition qd  left join Queue_Technician qt on qd.QUEUEID=qt.QUEUEID  join SdUser sd on ...

                      • Workaround for Time-stamp/snapshot report creation

                        Sample Requirement: To Compare request count that was open yesterday with the request count that was open the day before yesterday. Step 1: Add a new table in Analytics Plus using Import from Local DB. In this step you will create a new table using ...

                      • Make Request ID in Analytics Plus clickable - Hyper linking to SDP ticket

                        Here are the steps to add a column with Request ID as link to SDP tikcet. Step 1: Open a request from ServiceDesk Plus and copy the URL as shown below,  ( http://localhost:9090/WorkOrder.do?woMode=viewWO&woID=) Step 2: Create a custom formula in ...

                      • How to import description and resolution fields from ServiceDesk Plus to Analytics Plus?

                        Description details from the Requests' module are not available out-of-the box in Analytics Plus as it might contain HTML contents and cannot be displayed properly for reporting. However, if you wish to import description details into Analytics Plus, ...

                        Related Products