Version : 13004

DB: PGSQL

OUTPUT:

SELECT wo.WORKORDERID "Request ID",
       ti.FIRST_NAME "Technician",
       std.STATUSNAME "Request Status",
       pd.PRIORITYNAME "Priority",
       longtodate(wo.CREATEDTIME) "Created Time",
      adef.org_name "Account",
      cast((wo.TIMESPENTONREQ/1000 ) * '1 second'::interval as varchar) "TIme taken to Resolved HH:MM:SEC",
       au1.first_name "Changed From",
       au2.first_name "Changed To" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN sitedefinition sdef ON sdef.siteid=wo.siteid
 LEFT JOIN accountsitemapping asm ON asm.siteid=sdef.siteid
 LEFT JOIN accountdefinition adef ON adef.org_id=asm.accountid
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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN workorderhistory woh ON wo.workorderid=woh.workorderid
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN AaaUser aau1 ON aau1.USER_ID = woh.OPERATIONOWNERID
LEFT JOIN aaauser au1 ON cast(cast(wohd.prev_value AS varchar) AS int)=au1.user_id
LEFT JOIN aaauser au2 ON cast(cast(wohd.current_value AS varchar) AS int)=au2.user_id
WHERE wohd.COLUMNNAME IN ('OWNERID')