This will be helpful in auto closing change request after X days it is in review stage. This use case is more suitable in environments where ONLY Requesters (customers) are the Change Reviewers and they want the Change to be closed if no review is done after 7 days. In these cases, customers wants this to be auto closed just like Request closing rules.
Kindly execute the below query and save it after running it under Reports->New Query Report. Feed this saved Query as Arguments under Admin->Custom schedule.
SELECT chdt.CHANGEID AS "change_id" FROM ChangeDetails chdt LEFT JOIN Change_StageDefinition stageDef ON chdt.WFSTAGEID=stageDef.WFSTAGEID 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 where (stageDef.DISPLAYNAME = 'Review') and cdhd.COLUMNNAME in ( 'WFSTAGEID') and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(operationtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 2 group by chdt.CHANGEID
You can also verify the output of the query using the below query before scheduling.
SELECT chdt.CHANGEID AS "change_id",
max(stageDef.DISPLAYNAME) AS "Stage",
max(statusDef.STATUSDISPLAYNAME) AS "Status",
longtodate(max(operationtime)) "Operation time" FROM ChangeDetails chdt LEFT JOIN Change_StageDefinition stageDef ON chdt.WFSTAGEID=stageDef.WFSTAGEID 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 where (stageDef.DISPLAYNAME = 'Review') and cdhd.COLUMNNAME in ( 'WFSTAGEID') and
DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(operationtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 2 group by chdt.CHANGEID