SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Technician Changed From",aa.first_name as "Technician Changed To",longtodate(ti.ENDTIME) as "Technician Changed Time",
(CONVERT(varchar, (ti.TIMESPENT/1000)/86400) + ':' + CONVERT(varchar, DATEADD(ms, ((ti.TIMESPENT/1000)%86400)*1000,0),108)) as "TimeSpent in Previous Technician in Days:HH:MM:SS" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_Tech_Info ti ON woas.ASSESSMENTID = ti.ASSESSMENTID
LEFT JOIN AAAUser a ON ti.TECHNICIANID = a.USER_ID
LEFT JOIN AAAUser aa ON ti.NEXTTECHNICIANID = aa.USER_ID
WHERE
(
wo.ISPARENT = '1'
and ti.NEXTTECHNICIANID IS Not Null
)
ORDER BY
1,
woas.ASSESSMENTID asc