Version : 14001

DB : MSSQL

OUTPUT :

Query :

select woa.workorderid "RequestID",
wo.title "Subject",
pd.priorityname "Priority",
aauOwn.FIRST_NAME AS "Technician",
qd.queuename AS "Group",
sd1.statusname "From status",
sd2.statusname "To status",
longtodate(wsi.ENDTIME) "Time Status changed",
case when wsi.NEXTSTATUSID is NULL THEN cast(datediff(hh , dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (woa.executedtime/1000),'1970-01-01 00:00:00'),  getdate())%24 as varchar)+':'+cast(datediff(mm , dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (woa.executedtime/1000),'1970-01-01 00:00:00'),  getdate())%60 as varchar)
 ELSE CAST(((wsi.TIMESPENT)/3600000) AS VARCHAR(20)) +':'+
CAST((((wsi.TIMESPENT) % 3600000)/ 60000) AS VARCHAR(20)) END AS "Status TimeSpent Hrs : Mins"  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
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN SDUser sduOwn ON wos.ownerid = sduOwn.USERID
LEFT JOIN AaaUser aauOwn ON sduOwn.USERID=aauOwn.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
order by 1