Report Containing most recent note
Hello,
I'm trying to figure out how to have the most recent note in a report. I have been able to make a report that prints ALL the notes, but it is a huge report, so I just want the report to list the most recent report in the request. Here's some of what I have but haven't been able to get the most recent note.
SELECT DISTINCT wo.WORKORDERID AS "Request ID",
aau.FIRST_NAME AS "Requester",
wo.TITLE AS "Subject",
wotodesc.FULLDESCRIPTION AS "Description",
std.STATUSNAME AS "Request Status",
dpt.DEPTNAME AS "Department",
pd.PRIORITYNAME AS "Priority",
cd.CATEGORYNAME AS "Category",
scd.NAME AS "Subcategory",
note.NOTESID AS "ID",
note.NOTESTEXT AS "Last Note",
LONGTODATE(note.NOTESDATE) AS "Note Date",
aaauser.first_name AS "Note Added By",
LONGTODATE(wo.CREATEDTIME) AS "Created Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN Notes note ON wo.WORKORDERID=note.WORKORDERID
LEFT JOIN aaauser on note.userid=aaauser.user_id
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID GROUP BY wo.workorderid,note.notesid,wotodesc.FULLDESCRIPTION,qd.queuename,std.statusname,aau.first_name,dpt.deptname,pd.priorityname,cd.categoryname,scd.name,ti.first_name HAVING ((((((std.STATUSNAME::TEXT != 'Closed'::TEXT) AND (std.STATUSNAME::TEXT != 'Resolved'::TEXT)) OR (std.STATUSNAME::TEXT IS NULL)) AND (qd.QUEUENAME = 'Support') AND (note.NOTESID=MAX(note.NOTESID))) OR (((std.STATUSNAME::TEXT != 'Closed'::TEXT) AND (std.STATUSNAME::TEXT != 'Resolved'::TEXT)) OR (std.STATUSNAME::TEXT IS NULL))) AND (ti.FIRST_NAME = 'Support')AND (note.NOTESID=MAX(note.NOTESID))) AND wo.ISPARENT='1' limit 5000