--Check Older than 30 days



--DocumentData



DECLARE @YSRTIME Varchar(1000)

DECLARE @SQLCommand1 varchar(1000)

DECLARE @SQLCommand2 varchar(1000)



SET @YSRTIME = 30



SET @SQLCommand1 ='Select count(*) from DocumentData where RevisionId  in (select RevisionId from DocumentRevision where DocumentId in (select DocumentId from DocumentItem where DocumentTypeCode =''REPORTXML'' AND  CreationDate <'+'GETDATE()-'+ @YSRTIME+'))'



EXEC (@SQLCommand1)



SET @SQLCommand2 = 'WHILE 1=1 BEGIN;DELETE top(2000) from DocumentData where RevisionId  in (select RevisionId from DocumentRevision where DocumentId in (select DocumentId from DocumentItem where DocumentTypeCode =''REPORTXML'' AND  CreationDate <'+'GETDATE()-'+ @YSRTIME+'))'+'IF @@ROWCOUNT < 1  BREAK;END'



EXEC (@SQLCommand2)



--DocumentRevision





SET @SQLCommand1 = 'Select count(*) from DocumentRevision where DocumentId in (select DocumentId from DocumentItem where DocumentTypeCode=''REPORTXML'' AND  CreationDate <'+'GETDATE()-'+ @YSRTIME+' ) '



EXEC (@SQLCommand1)



SET @SQLCommand2 = 'WHILE 1=1 BEGIN; DELETE top(2000) from DocumentRevision where DocumentId in (select DocumentId from DocumentItem where DocumentTypeCode=''REPORTXML'' AND  CreationDate <'+'GETDATE()-'+ @YSRTIME+' ) '+'IF @@ROWCOUNT < 1 BREAK;END'



EXEC (@SQLCommand2)



--DocumentItem







SET @SQLCommand1 = 'Select count(*) from DocumentItem where DocumentTypeCode=''REPORTXML'' AND  CreationDate <'+'GETDATE()-'+ @YSRTIME



EXEC (@SQLCommand1)



SET @SQLCommand2 = 'WHILE 1=1 BEGIN; DELETE top(2000) from DocumentItem where DocumentTypeCode=''REPORTXML'' AND  CreationDate <'+'(GETDATE()-'+ @YSRTIME+')'+'IF @@ROWCOUNT < 1 BREAK;END'



EXEC (@SQLCommand2)
