Latest Public Notes query report

Latest Public Notes query report

I have the following query report which runs fine, however I want to remove the column "Last Notification Date", but I can't figure out what needs to stay and what needs to be removed just to get rid of that column.

  1. SELECT 
  2. max(ad.ORG_NAME) "Account",
  3. max(wo.WORKORDERID) "Request ID",
  4. max(aau.FIRST_NAME) "Requester",
  5. max(cd.CATEGORYNAME) "Category",
  6. max(scd.NAME) "Subcategory",
  7. max(wo.TITLE) "Subject",
  8. max(ti.FIRST_NAME) "Technician",
  9. max(std.STATUSNAME) "Request Status",
  10. longtodate(max(nt.NOTIFICATIONDATE))'Last Notification date',
  11. (select longtodate(max(note.NOTESDATE)) from Notes note where note.WORKORDERID = wo.WORKORDERID and note.ISPUBLIC = '1')'Last note date',
  12. (select (note1.NOTESTEXT) from notes note1 where note1.notesid in (select max(note.notesid) from Notes note group by note.workorderid) and note1.WORKORDERID = wo.WORKORDERID and note1.ISPUBLIC = '1')'Last Notes'

  13. FROM WorkOrder wo 
  14. LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
  15. LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
  16. LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
  17. LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID 
  18. LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
  19. LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
  20. LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
  21. LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 
  22. LEFT JOIN Notify_WorkOrder nwo ON nwo.WORKORDERID = wo.WORKORDERID 
  23. INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid 
  24. INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id
  25. LEFT JOIN Notification nt on nt.NOTIFICATIONID = (SELECT MAX(nwo.NOTIFICATIONID) FROM Notify_WorkOrder nwo WHERE nwo.WORKORDERID = wo.WORKORDERID) AND nt.senderid!=1

  26. WHERE wo.ISPARENT='1' and std.ispending=1

  27. and ((std.STATUSNAME <> 'Closed') or (std.STATUSNAME <> 'Resolved')) and ad.ORG_NAME = 'FlexiGroup'

  28. group by wo.WORKORDERID
  29. ORDER BY 10 
The DB in use is MSSQL.

I hope someone can help me out with this query.

Regards
Dale

                New to ADSelfService Plus?