Query to show request history for technicians, group and status changes (MSSQL & PGSQL)

Query to show request history for technicians, group and status changes (MSSQL & PGSQL)

Last tested on 14500 and above too

Technician

select woa.workorderid "RequestID",
ti1.FIRST_NAME "From technician",
ti2.FIRST_NAME "To technician",
longtodate(wti.timespent) "Time Spent" from workorder wo
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 order by 1

Group

select woa.workorderid "RequestID",
qd1.QUEUENAME  "From Group",
qd2.QUEUENAME  "To Group",
longtodate(wog.timespent) "Time Spent"  from workorder wo
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.groupid=qd2.QUEUEID order by 1 


Status

select woa.workorderid "RequestID", 
sd1.statusname "From status",
sd2.statusname "To status",
longtodate(wsi.timespent) "Time Spent"  from workorder wo 
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 order by 1


                  New to ADSelfService Plus?