--Delete for all previous tables
declare @i int=1
declare @cnt int=1
declare @date char(8)
declare @sql varchar(2000)
while @cnt <=10
begin
select @date = (select (case when len(cast(datepart(dd,(dateadd(wk,datediff(wk,6,getdate()),7))-(7*@i)) as varchar))=1 then '0' + cast(datepart(dd,(dateadd(wk,datediff(wk,6,getdate()),7))-(7*@i)) as varchar) else cast(datepart(dd,(dateadd(wk,datediff(wk,6,getdate()),7))-(7*@i)) as varchar) end) +
(case when len(cast(datepart(MM,(dateadd(wk,datediff(wk,6,getdate()),7))-(7*@i)) as varchar))=1 then '0' + cast(datepart(MM,(dateadd(wk,datediff(wk,6,getdate()),7))-(7*@i)) as varchar) else cast(datepart(MM,(dateadd(wk,datediff(wk,6,getdate()),7))-(7*@i)) as varchar) end) +
cast(datepart(yyyy,(dateadd(wk,datediff(wk,6,getdate()),7))-(7*@i)) as varchar))
set @i = @i+1
set @cnt = @cnt+1
print @date
set @sql = 'select count(*) '
set @sql = @sql + ' from DB..TB' + @date + convert(VARCHAR(8),'01234 ')
set @sql = @sql + ' where c1234=800.00 '
--print @sql
exec (@sql)
end
The requirement was to find and delete rows from a specific table (<tblDDMMYYYY>) every Monday and also past tables already created on Mondays.
Count is taken upto 10, means the loop will run till 10 weeks.
Check if Date is falling on Monday:
dateadd(wk,datediff(wk,6,getdate()),7)) - for monday of earlier dates
Count is taken upto 10, means the loop will run till 10 weeks.
Check if Date is falling on Monday:
dateadd(wk,datediff(wk,6,getdate()),7)) - for monday of earlier dates
Be the first to comment
You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.