Version : 11027

DB : Postgres

OUTPUT:

SELECT wo.WORKORDERID AS "Request ID",
 longtodate(wo.createdtime) as "Created Time",
 aau.FIRST_NAME AS "Requester",
 AaaContactInfo.EMAILID AS "Requester Email",
 comp.COMPONENTNAME AS "Product",
 std.STATUSNAME AS "Request Status",
 LONGTODATE(wo.DUEBYTIME) AS "DueBy Time",
 cast((wo.TIMESPENTONREQ/1000 ) * '1 second'::interval as varchar) "Time Elapsed",
 cast(extract(epoch from(now()::TIMESTAMP -  to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24 as integer) "No of Days (Age form Received)",
 wos.ISOVERDUE AS "Overdue Status",
 case when wos.ISOVERDUE = 'true' THEN cast(extract(epoch from(now()::TIMESTAMP -  to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))/3600/24 as integer) ELSE '0' END "No of Days (Overdue Time Elapsed)",
 au.FIRST_NAME "Last Escalation Technician ame" 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 SDUser td ON wos.OWNERID=td.USERID
 LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
 LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
 LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
 LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
 LEFT JOIN WorkOrder_product wop ON wo.WORKORDERID=wop.WORKORDERID
 LEFT JOIN componentdefinition comp ON wop.product_id=comp.COMPONENTID
 LEFT JOIN (select ns.escalationid,ns.itemid from requestslaescalations ns  WHERE  ns.id = (SELECT MAX(nts.id) FROM requestslaescalations nts WHERE nts.itemid=ns.itemid))woslaesc ON wo.WORKORDERID=woslaesc.itemid
 Left JOIN slaescalation slaesc1 on  woslaesc.escalationid = slaesc1.escalationid
 LEFT JOIN escalatetomediator escmed1 on slaesc1.escalatetoid = escmed1.escalatetoid
 left JOIN escalateton escon1 on escmed1.escalatetoid = escon1.escalatetoid
 LEFT join SdUser on  escon1.userid=SdUser.userid
 LEFT join aaaUser au on  SdUser.userid=au.user_id
 WHERE (wo.ISPARENT='1')

Note : Kindly use Date filter to avoid performance issue.