SELECT changetocab.changeid          "ChangeID", 
       chdt.title                    AS "Title", 
       ctdef.NAME                    AS "Change Type", 
       ownaaa.first_name             AS "Change Owner", 
       stageDef.displayname          AS "change Stage", 
       statusDef.statusdisplayname   AS "Change Status", 
       orgaaa.first_name             AS "Change Requester", 
       approvaldef.statusname        AS "Approval Status", 
       Longtodate(apprd.action_date) "Approval date", 
       apprd.comments                "Comments", 
       aaauser.first_name            "CAB Member", 
       apprd.statusname              "Status" FROM   changetocab 
       LEFT JOIN aaauser 
              ON changetocab.userid = aaauser.user_id 
       LEFT JOIN (SELECT apprstagetochange.changeid, 
                         approvalstatusdefinition.statusname FROM   approvalstage 
                         LEFT JOIN apprstagetochange 
                                ON approvalstage.approval_stageid = 
                         LEFT JOIN approvaldetails 
                                ON apprstagetochange.approval_stageid = 
                         LEFT JOIN approvalstatusdefinition 
                                ON approvaldetails.statusid = 
                                   approvalstatusdefinition.statusid) apprd 
              ON changetocab.userid = apprd.approverid 
                 AND changetocab.changeid = apprd.changeid 
       LEFT JOIN changedetails chdt 
              ON changetocab.changeid = chdt.changeid 
       LEFT JOIN change_fields cf 
              ON chdt.changeid = cf.changeid 
       LEFT JOIN approvalstatusdefinition approvaldef 
              ON chdt.appr_statusid = approvaldef.statusid 
       LEFT JOIN change_stagedefinition stageDef 
              ON chdt.wfstageid = stageDef.wfstageid 
       LEFT JOIN change_statusdefinition statusDef 
              ON chdt.wfstatusid = statusDef.wfstatusid 
       LEFT JOIN sduser orgsd 
              ON chdt.initiatorid = orgsd.userid 
       LEFT JOIN aaauser orgaaa 
              ON orgsd.userid = orgaaa.user_id 
       LEFT JOIN changetypedefinition ctdef 
              ON chdt.changetypeid = ctdef.changetypeid 
       LEFT JOIN sduser ownsd 
              ON chdt.technicianid = ownsd.userid 
       LEFT JOIN aaauser ownaaa 
              ON ownsd.userid = ownaaa.user_id 

          • Related Articles

          • How to assign CAB members to a change request automatically.

            This is a sample script to add CAB Members to a Change Request through Change Custom Triggers.The CAB Members can only be added manually to the Change Request ,under the Approval Tab, after it has been created.By using this script we will be able to ...
          • Dynamically add CAB based on Risk field value

            Requirement: Based on the Risk field value chosen, a set of CAB approvers have to be added to the Change UseCase: There will be different sets of CAB approvers available and when a Change is created with ‘Low/Medium’ risk a set of CAB approvers have ...