Query to get Last Conversation Details of Request

Build: 11020

Database: PostgreSQL

Output :

SELECT ti.FIRST_NAME AS "Support Rep",
wo.WORKORDERID AS "Request ID",
longtodate(wo.CREATEDTIME) AS "Created Time",
wo.TITLE AS "Subject",
aau.FIRST_NAME AS "Contact",
pd.PRIORITYNAME AS "Priority",
std.STATUSNAME AS "Request Status",
longtodate(wos.LAST_TECH_UPDATE) AS "Last Update Time",
(select conversationdescription.description from conversationdescription left join conversation con1 on conversationdescription.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid) "Last requester response",
to_char(from_unixtime((select con2.createdtime from conversation con2 where con2.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con2.workorderid=wo.workorderid)/1000),'DD/MM/YYYY HH24:MI:SS') "Last requester Date",
(select conversationrecipients.recipient_email from conversationrecipients left join conversation con1 on conversationrecipients.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid) "Last requester TO_CC_Field",
(note.NOTESTEXT) "Notes",
longtodate(note.notesdate) "Note Added date" 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 PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID = std.STATUSID
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID = wof.WORKORDERID
LEFT JOIN (select ns.WORKORDERID,ns.NOTESTEXT,ns.notesdate from Notes ns WHERE ns.NOTESID = (SELECT MAX(nts.NOTESID) FROM Notes nts WHERE nts.WORKORDERID=ns.WORKORDERID))note ON wo.WORKORDERID=note.WORKORDERID where wo.isparent = '1' GROUP by ti.FIRST_NAME,wo.workorderid,aau.first_name,pd.priorityname,std.statusname,wos.last_tech_update,note.notestext,note.notesdate
order by 2

Note : kindly use with date filter , without filter query might cause performance issue.

How to compare date column with auto filled date templates?