--SQL Script to Remove Report History from the Management Reporter database
--This script will remove any report history older than 60 days from the current day
-- Run this script against the Management Reporter database
DECLARE @CutoffDate datetime
SELECT @CutoffDate = GETDATE()
BEGIN TRANSACTION
DELETE ReportLineTransaction
WHERE ReportID IN
(SELECT ID
FROM Report
WHERE RepositoryID IN
(SELECT ID AS [RepositoryID]
FROM [Repository]
WHERE StatusType = 0 AND [Type] = 10 AND CreateDate <= DATEADD(day,-60,@CutoffDate)))
DELETE ReportLineFinancial
WHERE ReportID IN
(SELECT ID
FROM Report
WHERE RepositoryID IN
(SELECT ID AS [RepositoryID]
FROM [Repository]
WHERE StatusType = 0 AND [Type] = 10 AND CreateDate <= DATEADD(day,-60,@CutoffDate)))
DELETE ReportLineAccount
WHERE ReportID IN
(SELECT ID
FROM Report
WHERE RepositoryID IN
(SELECT ID AS [RepositoryID]
FROM [Repository]
WHERE StatusType = 0 AND [Type] = 10 AND CreateDate <= DATEADD(day,-60,@CutoffDate)))
DELETE Report
WHERE RepositoryID IN
(SELECT ID AS [RepositoryID]
FROM [Repository]
WHERE StatusType = 0 AND [Type] = 10 AND CreateDate <= DATEADD(day,-60,@CutoffDate))
DELETE [Repository]
WHERE StatusType = 0 AND [Type] = 10 AND CreateDate <= DATEADD(day,-60,@CutoffDate)
COMMIT TRANSACTION