Thursday, 10 January 2019

TSQL : Loop a date range

Useful for Incremental processing...
DECLARE @minDate DATE
SET @minDate = '1997-02-13'
DECLARE @maxDate DATE
SET @maxDate = GETDATE()
 
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @counterDate DATE
DECLARE @counterDateText VARCHAR(30)
DECLARE @MinCount INT
DECLARE @Stepsize INT
 
DECLARE @stepstart DATETIME
DECLARE @stepend DATETIME
DECLARE @duration bigint
DECLARE @message varchar(200)

SELECT @Counter = DATEDIFF(dd,@minDate,@maxDate)
SELECT @MinCount = 0
SELECT @Stepsize = 1
 
WHILE @Counter >= @MinCount 
 
BEGIN
SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
--RAISERROR (@CounterText, 10, 1) WITH NOWAIT  
 
SET @counterDate = DATEADD(dd,-@counter,@maxDate)
SET @counterDateText = CONVERT(VARCHAR(30),@counterDate,120)

RAISERROR (@counterDateText, 10, 1) WITH NOWAIT  

SET @stepstart = GETDATE()


-- do useful stuff here
-- WAITFOR DELAY '00:00:01'

SET @stepend = GETDATE()

SET @duration = DATEDIFF(ss,@stepstart,@stepend)
SET @message = 'Took ' + CONVERT(varchar(20),@duration) + ' seconds'
RAISERROR (@message, 10, 1) WITH NOWAIT  

 
SELECT @Counter = @Counter - @Stepsize 
END