When performing ETL functions, it’s often required to run processes with iteration over a given date range. Here are some examples of iterating over dates. These methods can also be applied to other cases where we want to do some iteration.

After my previous posts looking at dates, particularly Dates In SQL Server: Create Sample Date Ranges. I thought it worth looking at looping over dates. This is less about manipulating dates in tables, and more about targeted to methods for managing dates used in ETL processes. These examples use common iteration techniques, with an emphasis on dates.

Going round and round dates

There are a number of ways to create iteration within SQL Server, the two most common methods are loops, and cursors. For loops, I prefer to use a WHILE structure.

WHILE loop iterating dates

This is the most simple method, and familiar to people with a procedural background. The advantage is that it’s quicker and easier to create, the disadvantage is the opportunity for an infinite loop!

Be careful:

1. If you don’t do the increment correctly (at the end) you can create an infinite loop.
2. If the end date provided is before the start date you can create an infinite loop. A best practises implementation would check this before starting the loop.

DECLARE @DateStart DATE  = '01-JAN-2018';
DECLARE @DateEnd DATE    = '01-FEB-2018';
DECLARE @DateActive DATE = @DateStart;
 	 	 
PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Started Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateEnd, 120);
 	 	 
WHILE @DateActive <= @DateEnd
BEGIN
    PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Day: ' + CONVERT(VARCHAR(20), @DateActive, 120);
    ----------------------------------------------------------------------------
    -- Process
    ----------------------------------------------------------------------------
    /*
    EXECUTE [DB].[Schema].[stored_procedure] @DateVariable = @DateActive;
    */
    
    /*
    SELECT [DateField], COUNT(*) AS [count_rows]
      FROM [DB].[Schema].[table “” not found /]


     WHERE [DateField] = @DateActive
    ;
    */
    	 	 
    ----------------------------------------------------------------------------
    -- Increment
    ----------------------------------------------------------------------------
    SET @DateActive = DateAdd(Day, 1, @DateActive);
END;
 	 	 
PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Completed Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateEnd, 120);

 

Cursor iterating dates

When doing iteration in ETL processing this is the more common method, it prevents us from having infinite loops and allows us to iterate over any data type(s) easily including having more than one variable in the loop. The down side is it requires a little more code/logic.

--------------------------------------------------------------------------------
-- Create Sample Data (process control table)
--------------------------------------------------------------------------------

CREATE TABLE #SampleData
(
    date_col       DATE NOT NULL PRIMARY KEY,
    processed_date DATETIME2(2) -- Null is unprocessed
);

INSERT INTO #SampleData (date_col, processed_date) VALUES (GetDate() -3, GetDate() -1); -- Example of data already processed
INSERT INTO #SampleData (date_col, processed_date) VALUES (GetDate() -2, NULL);
INSERT INTO #SampleData (date_col, processed_date) VALUES (GetDate() -1, NULL);

--------------------------------------------------------------------------------
-- Craete Cursor
--------------------------------------------------------------------------------
DECLARE processing_date_cursor CURSOR FOR
SELECT date_col
  FROM #SampleData
 WHERE processed_date IS NULL
;

--------------------------------------------------------------------------------
-- Iterate Cursor
--------------------------------------------------------------------------------
DECLARE @DateActive DATE;

OPEN processing_date_cursor;
FETCH NEXT FROM processing_date_cursor INTO @DateActive;

IF @@FETCH_STATUS <> 0   
    PRINT 'Empty Cursor'       
WHILE @@FETCH_STATUS = 0  
BEGIN
    PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Day: ' + CONVERT(VARCHAR(20), @DateActive, 120);
    ----------------------------------------------------------------------------
    -- Process Start
    ----------------------------------------------------------------------------
    /*
    EXECUTE [DB].[Schema].[stored_procedure] @DateVariable = @DateActive;
    */
    
    ----------------------------------------------------------------------------
    -- For this example, log to table
    UPDATE #SampleData
       SET processed_date = GetDate()
     WHERE date_col = @DateActive
    ;
    
    ----------------------------------------------------------------------------
    -- Process End
    ----------------------------------------------------------------------------
    FETCH NEXT FROM processing_date_cursor INTO @DateActive;
END

CLOSE processing_date_cursor;  
DEALLOCATE processing_date_cursor;

See it in action over on rextester.com

Dynamic batches of dates

As I used this to process groups of dates previously, and I think it’s an interesting template to be able to copy/paste from I’ll include it here.

In my scenario, I had a large amount of history to reprocess. Doing the whole dataset was too much for a single transaction (it locked one table for too long), however day by day was also inefficient. I wanted to do windows of processing, but needed to experiment on the size of the date range in each batch. So I created the below:

--------------------------------------------------------------------------------
-- Define begin/end dates and batch size
--------------------------------------------------------------------------------
DECLARE @DateStart  DATE = '01-JAN-18';
DECLARE @DateEnd    DATE = '01-FEB-18';
DECLARE @BatchSize  INT  = 5;

PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Started Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateEnd, 120);

--------------------------------------------------------------------------------
-- Create batch variables and populate with first values
--------------------------------------------------------------------------------
DECLARE @DateActiveStart DATE = @DateStart;
DECLARE @FutureDate      DATE = DateAdd(DAY, @BatchSize - 1, @DateActiveStart); -- Note: subtract 1 from @BatchSize as we include the start date in the batch
DECLARE @DateActiveEnd   DATE = CASE WHEN @FutureDate < @DateEnd THEN @FutureDate ELSE @DateEnd END;

WHILE @DateActiveStart <= @DateEnd
BEGIN
    PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Batch: ' + CONVERT(VARCHAR(20), @DateActiveStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateActiveEnd, 120);
    ----------------------------------------------------------------------------
    -- Process
    ----------------------------------------------------------------------------
  --SELECT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Batch: ' + CONVERT(VARCHAR(20), @DateActiveStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateActiveEnd, 120) AS [output];
    /*
    EXECUTE [DB].[Schema].[stored_procedure] @DateVariable1 = @DateActiveStart, @DateVariable2 = @DateActiveEnd, ;
    */
 
    ----------------------------------------------------------------------------
    -- Increment
    ----------------------------------------------------------------------------
    SET @DateActiveStart = DateAdd(Day, 1, @DateActiveEnd);
    SET @FutureDate      = DateAdd(DAY, @BatchSize - 1, @DateActiveStart); -- Note: subtract 1 from @BatchSize as we include the start date in the batch
    SET @DateActiveEnd   = CASE WHEN @FutureDate < @DateEnd THEN @FutureDate ELSE @DateEnd END;
END;
 
PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Completed Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateEnd, 120);

See it in action over at http://rextester.com