Query to show Change Roles in change requests

Query to show Change Roles in change requests

MSSQL:

SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", LONGTODATE("chdt"."CREATEDTIME") AS "Created Time", LONGTODATE("chdt"."SCHEDULEDSTARTTIME") AS "Scheduled Start Time", LONGTODATE("chdt"."SCHEDULEDENDTIME") AS "Scheduled End", "ownaaa"."FIRST_NAME" AS "Change Owner", STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=8 FOR XML PATH ('')), 1, 1, '') 'Implementer', STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=9 FOR XML PATH ('')), 1, 1, '') 'Reviewer', STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=7 FOR XML PATH ('')), 1, 1, '') 'Line Manager',STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=5 FOR XML PATH ('')), 1, 1, '') 'Change Approver', "priodef1"."PRIORITYNAME" AS "Priority", "ctdef"."NAME" AS "Change Type", "approvaldef"."STATUSNAME" AS "Approval Status", "catadef"."CATEGORYNAME" AS "Category", "sdo"."NAME" AS "Site", "qd"."QUEUENAME" AS "Group", "stageDef"."DISPLAYNAME" AS "Stage", "statusDef"."STATUSDISPLAYNAME" AS "Status"  FROM "ChangeDetails" "chdt" 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 "ChangeTypeDefinition" "ctdef" ON "chdt"."CHANGETYPEID"="ctdef"."CHANGETYPEID" LEFT JOIN "ApprovalStatusDefinition" "approvaldef" ON "chdt"."APPR_STATUSID"="approvaldef"."STATUSID" LEFT JOIN "CategoryDefinition" "catadef" ON "chdt"."CATEGORYID"="catadef"."CATEGORYID" LEFT JOIN "SiteDefinition" "siteDef" ON "chdt"."SITEID"="siteDef"."SITEID" LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID" LEFT JOIN "QueueDefinition" "qd" ON "chdt"."GROUPID"="qd"."QUEUEID" LEFT JOIN "Change_StageDefinition" "stageDef" ON "chdt"."WFSTAGEID"="stageDef"."WFSTAGEID" LEFT JOIN "Change_StatusDefinition" "statusDef" ON "chdt"."WFSTATUSID"="statusDef"."WFSTATUSID"

PGSQL:

SELECT  cdt.changeid "Change Request ID", orgaaa.FIRST_NAME AS "Change Requester", ownaaa.FIRST_NAME AS "Change Owner", cmDef.FIRST_NAME AS "Change Manager", manager.name "Line Manager", implementer.name "Change Implementer", Reviewer.name "Change Reviewer", Approver.name "Change Approver" FROM ChangeDetails cdt   LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(us.first_name),',') AS name FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=8 GROUP BY ROLE.changeid)implementer ON cdt.changeid=implementer.changeid LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(us.first_name),',') AS name FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=9 GROUP BY ROLE.changeid)Reviewer ON cdt.changeid=reviewer.changeid LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(us.first_name),',') AS name FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=5 GROUP BY ROLE.changeid)Approver ON cdt.changeid=Approver.changeid LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(us.first_name),',') AS name FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=7 GROUP BY ROLE.changeid)manager ON cdt.changeid=manager.changeid LEFT JOIN SDUser orgsd ON cdt.INITIATORID=orgsd.USERID LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID LEFT JOIN SDUser ownsd ON cdt.TECHNICIANID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN AaaUser cmDef ON cdt.CHANGEMANAGERID=cmDef.USER_ID
          • Related Articles

          • Change Submission and Planning stage details

            PGSQL: SELECT  cdt.changeid "Change Request ID", orgaaa.FIRST_NAME AS "Change Requester", ownaaa.FIRST_NAME AS "Change Owner", cmDef.FIRST_NAME AS "Change Manager", manager.name "Line Manager", implementer.name "Change Implementer", Reviewer.name ...
          • Query to show solution details with approver and associated requests

            MSSQL: SELECT solution.solutionid "Solution ID" , Ad.org_name "Account", max(Solution.TITLE) "Solution Title", max(au.first_name) "Solution Approver", max(KB_Topics.TOPICNAME) "Solution Topic", max(Solution_Keywords.keyword) "Keywords", ...
          • Query to show an additional column action pending by in change request

            MSSQL: SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", LONGTODATE(chdt.CREATEDTIME) AS "Created Time", "ownaaa"."FIRST_NAME" AS "ChangeOwner", STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum  left ...
          • Query to show requests and its associations with Problems, Changes and Projects

            MSSQL: SELECT wo.WORKORDERID AS "Request ID", pim.problemid "Associated Problem ID", icm.changeid "Associated Change ID", wtp.projectid "Associated Project ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END ...
          • Query to show Approved tickets per Approver

            PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL "Approver Email", asd.stagename "Stage", ...