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 ADSelfService Plus?
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, ...