Query report to show Problem fields along with last added notes

Query report to show Problem fields along with last added notes

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", LONGTODATE(prob.CLOSEDTIME) AS "Closed Date", ad.ORG_NAME AS "Account", probf.UDF_CHAR2 AS "Country", sdo.NAME AS "Site", catadef.CATEGORYNAME AS "Category", subcatadef.NAME AS "Subcategory", prob.TITLE AS "Title", probdesc.FULL_DESCRIPTION AS "Description", impactdef.NAME AS "Impact", solwork.DESCRIPTION AS "Workaround", solres.DESCRIPTION AS "Solution", ownaaa.FIRST_NAME AS "Technician", priodef.PRIORITYNAME AS "Priority", statdef.STATUSNAME AS "Status", sdpnotes.description "Last notes added",  LONGTODATE(sdpnotes.recordeddate) "Last notes added date",notedaddedby.first_name "Last notes added by",wo.WORKORDERID AS "Associated Request IDs" FROM Problem prob LEFT JOIN Problem_Fields probf ON prob.PROBLEMID=probf.PROBLEMID 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 LEFT JOIN CategoryDefinition catadef ON prob.CATEGORYID=catadef.CATEGORYID LEFT JOIN SubCategoryDefinition subcatadef ON prob.SUBCATEGORYID=subcatadef.SUBCATEGORYID LEFT JOIN ProblemResolution probResol ON prob.PROBLEMID=probResol.PROBLEMID LEFT JOIN ImpactDefinition impactdef ON probResol.IMPACTID=impactdef.IMPACTID LEFT JOIN ProblemToDescription probdesc ON prob.PROBLEMID=probdesc.PROBLEMID LEFT JOIN SolutionToWorkAround probwork ON prob.PROBLEMID=probwork.PROBLEMID LEFT JOIN Solution solwork ON probwork.SOLUTIONID=solwork.SOLUTIONID LEFT JOIN SolutionToResolution probres ON prob.PROBLEMID=probres.PROBLEMID LEFT JOIN Solution solres ON probres.SOLUTIONID=solres.SOLUTIONID LEFT JOIN SiteDefinition siteDef ON prob.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID INNER JOIN AccountSiteMapping asm ON prob.SITEID=asm.SITEID INNER JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID 
LEFT JOIN problemtoincidentmapping pim ON prob.problemid=pim.problemid
LEFT JOIN workorder wo ON pim.workorderid=wo.workorderid
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
ORDER BY 1
          • Related Articles

          • Query to show request details and the last notes added to that request

            PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",qd.QUEUENAME "Group",std.STATUSNAME "Request Status",sdo.NAME "Site", Longtodate(wo.CREATEDTIME) "Created Time",Longtodate(wos.LAST_TECH_UPDATE) "Last Updte Time", notes.notestext ...
          • Query to show Last added worklog of a ticket _MSSQL

            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", wotodesc.FULLDESCRIPTION AS ...
          • Query to show problem time spent along with last activity on a problem

            MSSQL: 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 ...
          • Query to show the last worklog added in a ticket

            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",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show Last added worklog of a ticket

            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",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...