Version : 14300

DB :  PGSQL

OUTPUT :

SELECT wo.WORKORDERID "Request ID",
(aau.FIRST_NAME) "Requester",
(wo.TITLE) "Subject",
(ti.FIRST_NAME) "Technician",
LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Updated Time",
lastby.first_name "Last Updated By",
longtodate(wo.CREATEDTIME) "Created Time",
longtodate(c.notificationdate)"Conversation Created On" ,
c.notificationtitle "Conversation title" ,
longtodate(n.notificationdate)"First Response time" ,
c.first_name "conversation by",
n.notificationtitle "First Response Email Title",
n.first_name "Sender name",
sdo.NAME AS "Site",
ad.org_name "Account" 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 StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
left join accountsitemapping asm on asm.siteid = wo.siteid
Left join accountdefinition ad on asm.accountid = ad.org_id
Left Join WorkOrderHistory wh on ((wos.LAST_TECH_UPDATE = wh.operationtime) and  (wo.workorderid = wh.workorderid))
left JOIN aaaUser lastby on wh.operationownerid = lastby.user_id
LEFT JOIN (select con_wo.workorderid,con.notificationtitle ,con.notificationdate,au1.first_name from requestnotification con LEFT JOIN aaauser au1 on con.senderid=au1.user_id LEFT JOIN notify_workorder con_wo ON con.notificationid= con_wo.notificationid where con.notificationtype = 'conversation' ) c on c.workorderid=wo.workorderid
LEFT JOIN (select con_wo.workorderid,con.notificationtitle ,con.notificationdate,au1.first_name from requestnotification con LEFT JOIN aaauser au1 on con.senderid=au1.user_id LEFT JOIN notify_workorder con_wo ON con.notificationid= con_wo.notificationid where con.notificationtype = 'reply') n on n.workorderid=wo.workorderid
order by 1