Microsoft SQL Server – Complex Cleanup

Today my team manager asked me to create small interesting sql script. This script recieves time interval on input, splits given time interval into smaller parts (let’s say 24 hours per interval), counts total number of records for each interval, outputs this information and deletes records. Script mustn’t delete top”fresh” records though. E.g. manager would like to specify offset parameter, so records from interval [today-offset;today] mustn’t be deleted. Key parameters for this script are @offset, @cleanupDepth and @tableName.

declare @currentDate datetime
declare @previousDate datetime
declare @minDate datetime
declare @recordsCount int
declare @out_str varchar(255)
declare @sqlString nvarchar(2048)
declare @params nvarchar(4000)

–how many days will be left in table
declare @offset int
set @offset = 45

–depth of clean up in days
declare @cleanupDepth int;
set @cleanupDepth = 90

–setup table name
declare @tableName varchar(255)
set @tableName = ‘dbo.TEST_TABLE’

set @currentDate = DATEADD(DAY, -@offset, GETDATE())
set @minDate = DATEADD(DAY,-@cleanupDepth,@currentDate)

–fetch min date from table
declare @tableMinDate datetime
set @sqlString = ‘select @min_date = min(timestamp) from ‘ + @tableName
set @params = ‘@min_date datetime OUTPUT’
exec sp_executesql @sqlString, @params, @min_date = @tableMinDate OUTPUT

–check whether actual data in table has min date greater then @minDate
if @tableMinDate > @minDate
set @minDate = @tableMinDate

while ( @currentDate > @minDate )
begin

–get output string
set @out_str = ‘Date: [‘ + CONVERT(VARCHAR, @currentDate, 102) + ‘ – ‘
+ CONVERT(VARCHAR, @previousDate, 102) + ‘]’

–now we have interval [currentDate;perviousDate]
set @previousDate = @currentDate
set @currentDate = DATEADD(DAY,-1,@currentDate)

set @sqlString =
N’ select @cnt = count(1) from ‘ + @tableName +
N’ where timestamp >= @currentDate and timestamp < @previousDate' set @params = N'@currentDate datetime, ' + N'@previousDate datetime, ' + N'@cnt int OUTPUT' exec sp_executesql @sqlString, @params, @currentDate, @previousDate, @cnt = @recordsCount OUTPUT set @out_str = @out_str + ' Records count: ' + convert(varchar, @recordsCount) print @out_str --clear records set @sqlString = N' delete from ' + @tableName + N' where timestamp >= @startDate and timestamp < @endDate' set @params = N'@startDate datetime, ' + N'@endDate datetime ' exec sp_executesql @sqlString, @params, @currentDate, @previousDate end [/sourcecode] This script calculates current date. Then it skips @offset days from current date and starts iterate day by day in the past until @cleanupDepth is reached. For each day script counts total number of records and outputs it, then records are deleted.

This script can be easily modified to perform interval-based actions. E.g. count total number of records for each day lets say from 10a.m. to 11a.m. and perform some actions.