Problem and Associated incidents with Request id

Problem and Associated incidents with Request id

MSSQL


SELECT "prob"."PROBLEMID" AS "Problem ID",
       max("prob"."TITLE") AS "Title",
       longtodate(max("prob"."REPORTEDTIME")) AS "Reported Date",
       max("prob"."DUEBYTIME") AS "DueBy Date",
       max("orgaaa"."FIRST_NAME") AS "Reported by",
       max("probf"."UDF_CHAR1") AS "Group",
       max("ownaaa"."FIRST_NAME") AS "Technician",
       max("priodef"."PRIORITYNAME") AS "Priority",
       max("statdef"."STATUSNAME") AS "Status",
       max("catadef"."CATEGORYNAME") AS "Category",
       max("subcatadef"."NAME") AS "Subcategory",
       max(ps.rootcause) "Root Cause",
       count(pti.WORKORDERID) "Count of Associated Incidents",

  (SELECT STUFF(
                  (SELECT cast(pt1.workorderid AS varchar) + char(10)
                   FROM ProblemToIncidentMapping pt1
                   WHERE pt1.PROBLEMID=prob.PROBLEMID
                     FOR XML PATH ('')), 1, 0, '')) "Associated Incidents" 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 ps ON ps. PROBLEMID=prob.problemid
LEFT JOIN ProblemToIncidentMapping pti ON prob.PROBLEMID=pti.PROBLEMID

                  New to ADSelfService Plus?