-- Set 
USE EFTDB

DECLARE @DeleteTo DATETIME, @DeleteScope VARCHAR(20), @sqlstr nVARCHAR(2000)

-- @DeleteTo indicates the 'purge through' date
-- @DeleteScope indicates whether to purge a certain number of records or a percentage of the qualifying records.
-- This allows you to 'whittle away' records on a production server
-- If you aren't concerned that the server might be overloaded, set @DeleteScope to a large number such as 2000000000

--SELECT @DeleteTo = '1/1/2006', @DeleteScope = '50%'
--SELECT @DeleteTo = '1/1/2007', @DeleteScope = '1000'
--SELECT @DeleteTo = '1/1/2007', @DeleteScope = '2000000000'



SELECT @DeleteTo = '1/1/2007', @DeleteScope = '1000'

IF RIGHT(@DeleteScope,1) = '%'
SET @DeleteScope = 'TOP (' + LTRIM(RTRIM(LEFT(@DeleteScope,LEN(@DeleteScope)-1))) + ') PERCENT'
ELSE
SET @DeleteScope = 'TOP (' + LTRIM(RTRIM(@DeleteScope)) + ')'

SET @sqlstr = 'DELETE ' + @DeleteScope + ' from [tbl_ProtocolCommands] where Time_stamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' from [tbl_Actions] where Time_stamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' from [tbl_EventRules] from [tbl_EventRules]  LEFT OUTER JOIN
                      tbl_Actions ON tbl_EventRules.EventID = tbl_Actions.EventID where tbl_eventrules.Time_stamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''' AND (tbl_Actions.EventID IS NULL)'
PRINT @sqlstr
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' from [tbl_ClientOperations] where Time_stamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' from [tbl_SocketConnections] where Time_stamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' from [tbl_Authentications] where Time_stamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' from [tbl_CustomCommands] where Time_stamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr

-- AS2 Stuff, added 8/5/08 by Mike Fuller
SET @sqlstr = 'DELETE ' + @DeleteScope + ' FROM [tbl_AS2Actions] WHERE TimeStamp < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' FROM [tbl_AS2Files] WHERE TransactionID IN (SELECT TransactionID FROM [tbl_AS2Transactions] WHERE StartTime < ''' + CAST(@DeleteTo AS VARCHAR) + ''') AND FileID NOT IN (SELECT DISTINCT FileID FROM [tbl_AS2Actions])'
EXECUTE sp_executesql @sqlstr
SET @sqlstr = 'DELETE ' + @DeleteScope + ' FROM [tbl_AS2Transactions] WHERE StartTime < ''' + CAST(@DeleteTo AS VARCHAR) + ''''
EXECUTE sp_executesql @sqlstr