Change Management - history of status changes made on a change request

Change Management - history of status changes made on a change request

SELECT chdt.changeid                       "Change ID", 
       chdt.title                          "Title", 
       orgaaa.first_name                   "Requested by", 
       ownaaa.first_name                   "Technician", 
       Longtodate(chdt.createdtime)        "Created Time", 
       Longtodate(chdt.scheduledstarttime) "Scheduled Start Time", 
       Longtodate(chdt.scheduledendtime)   "Scheduled End", 
       Longtodate(chdt.completedtime)      "Completed Time", 
       statusDef.statusdisplayname         "Change Status", 
       aau1.first_name                     "Changed By", 
       Longtodate(cdh.operationtime)       "Changed On", 
       stdp.statusdisplayname              "Changed From", 
       stdc.statusdisplayname              "Changed To" FROM   changedetails chdt 
       LEFT JOIN change_stagedefinition stageDef 
              ON chdt.wfstageid = stageDef.wfstageid 
       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_statusdefinition statusDef 
              ON chdt.wfstatusid = statusDef.wfstatusid 
       LEFT JOIN changehistory cdh 
              ON chdt.changeid = cdh.changeid 
       LEFT JOIN changehistorydiff cdhd 
              ON cdh.historyid = cdhd.historyid 
       LEFT JOIN aaauser aau1 
              ON aau1.user_id = cdh.operationownerid 
       LEFT JOIN change_statusdefinition stdp 
              ON cdhd.prev_value = stdp.wfstatusid 
       LEFT JOIN change_statusdefinition stdc 
              ON cdhd.current_value = stdc.wfstatusid 
WHERE  cdhd.columnname IN ( 'WFSTATUSID' ) 
ORDER  BY 1


Click this link to navigate to the next report.‚Äč