Change Management - report on change approval details

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.‚Äč