Change Management - report on change roles associated to users on a given change
PGSQL:
SELECT chdt.changeid "Change ID",
chdt.title "Title",
orgaaa.first_name "Change Requester",
ownaaa.first_name "Change Owner",
cmDef.first_name "Change Manager",
stageDef.displayname "Stage",
statusDef.statusdisplayname "Status",
implementer.NAME "Change Implementer",
line.NAME "Change line manager",
rev.NAME "Change Reviewer",
app.NAME "Change Approver" FROM changedetails chdt
LEFT JOIN sduser orgsd
ON chdt.initiatorid = orgsd.userid
LEFT JOIN aaauser orgaaa
ON orgsd.userid = orgaaa.user_id
LEFT JOIN sduser ownsd
ON chdt.technicianid = ownsd.userid
LEFT JOIN aaauser ownaaa
ON ownsd.userid = ownaaa.user_id
LEFT JOIN change_stagedefinition stageDef
ON chdt.wfstageid = stageDef.wfstageid
LEFT JOIN change_statusdefinition statusDef
ON chdt.wfstatusid = statusDef.wfstatusid
LEFT JOIN aaauser cmDef
ON chdt.changemanagerid = cmDef.user_id
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)implementer
ON chdt.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 = 6
GROUP BY ROLE.changeid)line
ON chdt.changeid = line.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 = 8
GROUP BY ROLE.changeid)rev
ON chdt.changeid = rev.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)app
ON chdt.changeid = app.changeid
MSSQL:
SELECT chdt.changeid "Change ID",
chdt.title "Title",
orgaaa.first_name "Change Requester",
ownaaa.first_name "Change Owner",
cmDef.first_name "Change Manager",
stageDef.displayname "Stage",
statusDef.statusdisplayname "Status",
(SELECT STUFF((SELECT us.first_name + CHAR(10) FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=7 and chdt.CHANGEID=role.CHANGEID FOR XML PATH ('')), 1, 0, '')) 'Implementer',
(SELECT STUFF((SELECT us.first_name + CHAR(10) FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=5 and chdt.CHANGEID=role.CHANGEID FOR XML PATH ('')), 1, 0, '')) 'ChangeApprover',
(SELECT STUFF((SELECT us.first_name + CHAR(10) FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=8 and chdt.CHANGEID=role.CHANGEID FOR XML PATH ('')), 1, 0, '')) 'ChangeReviewer' FROM changedetails chdt
LEFT JOIN sduser orgsd
ON chdt.initiatorid = orgsd.userid
LEFT JOIN aaauser orgaaa
ON orgsd.userid = orgaaa.user_id
LEFT JOIN sduser ownsd
ON chdt.technicianid = ownsd.userid
LEFT JOIN aaauser ownaaa
ON ownsd.userid = ownaaa.user_id
LEFT JOIN change_stagedefinition stageDef
ON chdt.wfstageid = stageDef.wfstageid
LEFT JOIN change_statusdefinition statusDef
ON chdt.wfstatusid = statusDef.wfstatusid
LEFT JOIN aaauser cmDef
ON chdt.changemanagerid = cmDef.user_id order by 1
Click this link to navigate to the next report.