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.
- SELECT
- max(ad.ORG_NAME) "Account",
- max(wo.WORKORDERID) "Request ID",
- max(aau.FIRST_NAME) "Requester",
- max(cd.CATEGORYNAME) "Category",
- max(scd.NAME) "Subcategory",
- max(wo.TITLE) "Subject",
- max(ti.FIRST_NAME) "Technician",
- max(std.STATUSNAME) "Request Status",
- longtodate(max(nt.NOTIFICATIONDATE))'Last Notification date',
- (select longtodate(max(note.NOTESDATE)) from Notes note where note.WORKORDERID = wo.WORKORDERID and note.ISPUBLIC = '1')'Last note date',
- (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'
- 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 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 StatusDefinition std ON wos.STATUSID=std.STATUSID
- LEFT JOIN Notify_WorkOrder nwo ON nwo.WORKORDERID = wo.WORKORDERID
- INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
- INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id
- LEFT JOIN Notification nt on nt.NOTIFICATIONID = (SELECT MAX(nwo.NOTIFICATIONID) FROM Notify_WorkOrder nwo WHERE nwo.WORKORDERID = wo.WORKORDERID) AND nt.senderid!=1
- WHERE wo.ISPARENT='1' and std.ispending=1
- and ((std.STATUSNAME <> 'Closed') or (std.STATUSNAME <> 'Resolved')) and ad.ORG_NAME = 'FlexiGroup'
- group by wo.WORKORDERID
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?