Query report to show Problem fields along with last added notes

Query report to show Problem fields along with last added notes


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
          • 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 ...