Description:
How do you get a report on Evolve that allows you to have all the transaction and query scripts not used over a period of time?
Works with 20x
Solution:
User can Create a new Report as per their requirement; use the below query to create the report. Please create in Quality first.
Select Distinct (RPT_SolutionView.Title) AS ‘ScriptName’,RPT_SolutionView.Description AS ‘Description’,RPT_AppGroupView.Appname AS RPT_AppGroupView_Appname FROM [RPT_SolutionView]
JOIN RPT_APPGroupView on RPT_SolutionView.AppId = RPT_APPGroupView.Appid where RPT_SolutionView.ScriptType in (0,1)
AND RPT_SolutionView.Title NOT In
(SELECT distinct RPT_SolutionView.Title
FROM RPT_SolutionView
JOIN [RPT_TransactionRoiView] txr ON RPT_SolutionView.AppId = txr.ReferenceId
where RPT_SolutionView.ScriptType = 0 and txr.TxrTitle like CONCAT(‘%’, RPT_SolutionView.Title, ‘%’)
and DATEDIFF(MINUTE,txr.ActivityDateTime, GETUTCDATE()) >0)
AND RPT_SolutionView.Title NOT In
(SELECT distinct RPT_SolutionView.Title
FROM RPT_SolutionView RPT_SolutionView
JOIN [RPT_QueryRoiView] qr ON RPT_SolutionView.AppId = qr.ReferenceId
where RPT_SolutionView.ScriptType = 1 and qr.QueryFileName like CONCAT(‘%’, RPT_SolutionView.Title, ‘%’)
and DATEDIFF(MINUTE,qr.DateTimeOfRun, GETUTCDATE()) >0)
