Community and Support

            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')
            Updated: 01 Dec 2017 07:10 AM
            Helpful?  
            Help us to make this article better
            0 0