Query to show last notes added in change request. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Change Request:
SELECT ad.ORG_NAME AS "Account", chdt.CHANGEID AS "Change ID", chdt.CREATEDTIME AS "Created Time", ownaaa.FIRST_NAME AS "Change Owner", priodef1.PRIORITYNAME AS "Priority", chdt.TITLE AS "Title", orgaaa.FIRST_NAME AS "Change Requester",sdpnotes.description "Last notes added", LONGTODATE(sdpnotes.recordeddate) "Last notes added date",notedaddedby.first_name "Last notes added by" FROM ChangeDetails chdt
LEFT JOIN SDUser orgsd ON chdt.INITIATORID=orgsd.USERID
LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID
LEFT JOIN SDUser ownsd ON chdt.TECHNICIANID=ownsd.USERID
LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID
LEFT JOIN PriorityDefinition priodef1 ON chdt.PRIORITYID=priodef1.PRIORITYID
LEFT JOIN changetonotes ctn on ctn.changeid=chdt.changeid
LEFT JOIN sdpnotes on sdpnotes.notesid=ctn.notesid
INNER JOIN AccountSiteMapping asm ON chdt.SITEID=asm.SITEID
INNER JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN aaauser notedaddedby on notedaddedby.user_id=sdpnotes.userid
where sdpnotes.notesid = (select max(changetonotes.notesid) from changetonotes where changetonotes.changeid=ctn.changeid) or ctn.changeid is NULL
New to ADSelfService Plus?
Related Articles
Query report to show Problem fields along with last added notes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT prob.PROBLEMID AS "Problem ID", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", orgaaa.FIRST_NAME AS "Reported by", LONGTODATE(prob.DUEBYTIME) AS "DueBy Date", ...Query to show last notes added in request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) select wo.workorderid "request id", max(wo.title) "subject", max(qd.queuename) "group", max(std.statusname) "request status", max(pd.priorityname) "priority", max(sdo.name) "site", max(wo.createdtime) ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query to list out the notes added to the tickets
QUERY: To list of the notes added to the tickets while generating report report 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", ...