Query to show last note added in problem request. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Problem Request:
SELECT ad.ORG_NAME AS "Account", prob.PROBLEMID AS "Problem ID", statdef.STATUSNAME AS "Status", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", ownaaa.FIRST_NAME AS "Technician", priodef.PRIORITYNAME AS "Priority", prob.TITLE AS "Title", orgaaa.FIRST_NAME AS "Reported by",sdpnotes.description "Last notes added", LONGTODATE(sdpnotes.recordeddate) "Last notes added date",notedaddedby.first_name "Last notes added by" FROM Problem prob
LEFT JOIN SDUser orgsd ON prob.ORIGINATORID=orgsd.USERID
LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID
LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID
LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID
LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID
LEFT JOIN PriorityDefinition priodef ON prob.PRIORITYID=priodef.PRIORITYID
INNER JOIN AccountSiteMapping asm ON prob.SITEID=asm.SITEID
INNER JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN problemtonotes ptm on ptm.problemid=prob.problemid
LEFT JOIN sdpnotes on sdpnotes.notesid=ptm.notesid
LEFT JOIN aaauser notedaddedby on notedaddedby.user_id=sdpnotes.userid
where sdpnotes.notesid = (select max(problemtonotes.notesid) from problemtonotes where problemtonotes.problemid=ptm.problemid) or ptm.problemid 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 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 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 problem time spent along with last activity on a problem (MSSQL)
Tested in MSSQL build (14306) SELECT "prob"."PROBLEMID" AS "Problem ID", "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status", "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...