Change Management - report on change approval details
SELECT chdt.changeid AS "Change ID",
chdt.title AS "Title",
orgaaa.first_name AS "Change Requester",
qd.queuename AS "Group",
ownaaa.first_name AS "Change Owner",
catadef.categoryname AS "Category",
priodef1.priorityname AS "Priority",
Longtodate(chdt.createdtime) AS "Created Time",
Longtodate(chdt.completedtime) AS "Completed Time",
statusDef.statusdisplayname "Status",
stageDef.displayname "Stage",
approvaldef.statusname "Approval Status",
aaa.first_name "Change Approved by",
Longtodate(approvaldetails.action_date) "Change Approved On",
approvaldetails.email "Approver Email",
Longtodate(approvalstage.sent_date) "Approval Sent date",
approvaldetails.comments "Comments" 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 prioritydefinition priodef1
ON chdt.priorityid = priodef1.priorityid
LEFT JOIN categorydefinition catadef
ON chdt.categoryid = catadef.categoryid
LEFT JOIN queuedefinition qd
ON chdt.groupid = qd.queueid
LEFT JOIN change_statusdefinition statusDef
ON chdt.wfstatusid = statusDef.wfstatusid
LEFT JOIN change_stagedefinition stageDef
ON chdt.wfstageid = stageDef.wfstageid
LEFT JOIN approvalstatusdefinition approvaldef
ON chdt.appr_statusid = approvaldef.statusid
LEFT JOIN apprstagetochange
ON chdt.changeid = apprstagetochange.changeid
LEFT JOIN approvaldetails
ON apprstagetochange.approval_stageid =
approvaldetails.approval_stageid
LEFT JOIN approvalhistory aph
ON approvaldetails.approvalid = aph.approvalid
JOIN sduser sd
ON approvaldetails.approverid = sd.userid
LEFT JOIN aaauser aaa
ON sd.userid = aaa.user_id
LEFT JOIN approvalstage
ON approvaldetails.approval_stageid =
approvalstage.approval_stageid
Click this link to navigate to the next report.