Version : 10609

DB : MSSQL

OUTPUT :

SELECT "wo"."WORKORDERID" AS "Request ID",
sisd.STATUSNAME as "Changed From",
sinsd.STATUSNAME as "Changed To",
case when si.NEXTSTATUSID is NULL THEN
cast(datediff(d , dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (woas.executedtime/1000),'1970-01-01 00:00:00'),  getdate()) as varchar)+':'+cast(datediff(hh , dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (woas.executedtime/1000),'1970-01-01 00:00:00'),  getdate())%24 as varchar)+':'+cast(datediff(mm , dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (woas.executedtime/1000),'1970-01-01 00:00:00'),  getdate())%60 as varchar)
 ELSE CAST(((si.TIMESPENT)/3600000) AS VARCHAR(20)) +':'+
CAST((((si.TIMESPENT) % 3600000)/ 60000) AS VARCHAR(20)) END AS "Open Status TimeSpent (DD:HH:MM)",
"std"."STATUSNAME" AS "Request Status"  FROM "WorkOrder" "wo"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID"
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_Status_Info si ON woas.ASSESSMENTID = si.ASSESSMENTID
LEFT JOIN StatusDefinition sisd ON si.STATUSID = sisd.STATUSID
LEFT JOIN StatusDefinition sinsd ON si.NEXTSTATUSID = sinsd.STATUSID
WHERE (wo.ISPARENT='1') and si.STATUSID IS Not Null and sisd.STATUSNAME = 'Open'