Change Management - report on change stages history

Change Management - report on change stages history

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", 
       stagedef.NAME                       "Change Status", 
       aau1.first_name                     "Changed By", 
       Longtodate(cdh.operationtime)       "Changed On", 
       stdp.NAME                           "Changed From", 
       stdc.NAME                           "Changed To" 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.stageid = stagedef.wfstageid 
       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_stagedefinition stdp 
              ON Cast(cdhd.prev_value AS VARCHAR) = Cast( 
                 stdp.wfstageid AS VARCHAR) 
       LEFT JOIN change_stagedefinition stdc 
              ON Cast(Cast(cdhd.current_value AS VARCHAR) AS 
                 INT) = stdc.wfstageid 
WHERE  cdhd.columnname IN ( 'WFSTAGEID' ) 
ORDER  BY 1 

Click this link to navigate to the next report.​​