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')