Management Reporter-Remove Report History

--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