When performing ETL functions, it’s often required to run processes\u00a0with 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.<\/p>\n
After my previous posts\u00a0looking at dates, particularly\u00a0Dates In SQL Server: Create Sample Date Ranges<\/a>. 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.<\/p>\n 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.<\/p>\n 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!<\/p>\n Be careful:<\/p>\n <\/p>\n 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.<\/p>\n See it in action over on\u00a0rextester.com<\/a><\/p>\n 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.<\/p>\n 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:<\/p>\nGoing round and round dates<\/h1>\n
WHILE loop iterating dates<\/h2>\n
\n
DECLARE @DateStart DATE = '01-JAN-2018';\r\nDECLARE @DateEnd DATE = '01-FEB-2018';\r\nDECLARE @DateActive DATE = @DateStart;\r\n \t \t \r\nPRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Started Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateEnd, 120);\r\n \t \t \r\nWHILE @DateActive <= @DateEnd\r\nBEGIN\r\n PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Day: ' + CONVERT(VARCHAR(20), @DateActive, 120);\r\n ----------------------------------------------------------------------------\r\n -- Process\r\n ----------------------------------------------------------------------------\r\n \/*\r\n EXECUTE [DB].[Schema].[stored_procedure] @DateVariable = @DateActive;\r\n *\/\r\n \r\n \/*\r\n SELECT [DateField], COUNT(*) AS [count_rows]\r\n FROM [DB].[Schema].[table \u201c\u201d not found \/]
\n\r\n WHERE [DateField] = @DateActive\r\n ;\r\n *\/\r\n \t \t \r\n ----------------------------------------------------------------------------\r\n -- Increment\r\n ----------------------------------------------------------------------------\r\n SET @DateActive = DateAdd(Day, 1, @DateActive);\r\nEND;\r\n \t \t \r\nPRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Completed Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateEnd, 120);<\/pre>\nCursor\u00a0iterating dates<\/h2>\n
--------------------------------------------------------------------------------\r\n-- Create Sample Data (process control table)\r\n--------------------------------------------------------------------------------\r\n\r\nCREATE TABLE #SampleData\r\n(\r\n date_col DATE NOT NULL PRIMARY KEY,\r\n processed_date DATETIME2(2) -- Null is unprocessed\r\n);\r\n\r\nINSERT INTO #SampleData (date_col, processed_date) VALUES (GetDate() -3, GetDate() -1); -- Example of data already processed\r\nINSERT INTO #SampleData (date_col, processed_date) VALUES (GetDate() -2, NULL);\r\nINSERT INTO #SampleData (date_col, processed_date) VALUES (GetDate() -1, NULL);\r\n\r\n--------------------------------------------------------------------------------\r\n-- Craete Cursor\r\n--------------------------------------------------------------------------------\r\nDECLARE processing_date_cursor CURSOR FOR\r\nSELECT date_col\r\n FROM #SampleData\r\n WHERE processed_date IS NULL\r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Iterate Cursor\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateActive DATE;\r\n\r\nOPEN processing_date_cursor;\r\nFETCH NEXT FROM processing_date_cursor INTO @DateActive;\r\n\r\nIF @@FETCH_STATUS <> 0 \r\n PRINT 'Empty Cursor' \r\nWHILE @@FETCH_STATUS = 0 \r\nBEGIN\r\n PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Day: ' + CONVERT(VARCHAR(20), @DateActive, 120);\r\n ----------------------------------------------------------------------------\r\n -- Process Start\r\n ----------------------------------------------------------------------------\r\n \/*\r\n EXECUTE [DB].[Schema].[stored_procedure] @DateVariable = @DateActive;\r\n *\/\r\n \r\n ----------------------------------------------------------------------------\r\n -- For this example, log to table\r\n UPDATE #SampleData\r\n SET processed_date = GetDate()\r\n WHERE date_col = @DateActive\r\n ;\r\n \r\n ----------------------------------------------------------------------------\r\n -- Process End\r\n ----------------------------------------------------------------------------\r\n FETCH NEXT FROM processing_date_cursor INTO @DateActive;\r\nEND\r\n\r\nCLOSE processing_date_cursor; \r\nDEALLOCATE processing_date_cursor;\r\n<\/pre>\n
Dynamic batches of dates<\/h1>\n
--------------------------------------------------------------------------------\r\n-- Define begin\/end dates and batch size\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateStart DATE = '01-JAN-18';\r\nDECLARE @DateEnd DATE = '01-FEB-18';\r\nDECLARE @BatchSize INT = 5;\r\n\r\nPRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Started Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateEnd, 120);\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create batch variables and populate with first values\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateActiveStart DATE = @DateStart;\r\nDECLARE @FutureDate DATE = DateAdd(DAY, @BatchSize - 1, @DateActiveStart); -- Note: subtract 1 from @BatchSize as we include the start date in the batch\r\nDECLARE @DateActiveEnd DATE = CASE WHEN @FutureDate < @DateEnd THEN @FutureDate ELSE @DateEnd END;\r\n\r\nWHILE @DateActiveStart <= @DateEnd\r\nBEGIN\r\n PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Batch: ' + CONVERT(VARCHAR(20), @DateActiveStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateActiveEnd, 120);\r\n ----------------------------------------------------------------------------\r\n -- Process\r\n ----------------------------------------------------------------------------\r\n --SELECT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Batch: ' + CONVERT(VARCHAR(20), @DateActiveStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateActiveEnd, 120) AS [output];\r\n \/*\r\n EXECUTE [DB].[Schema].[stored_procedure] @DateVariable1 = @DateActiveStart, @DateVariable2 = @DateActiveEnd, ;\r\n *\/\r\n \r\n ----------------------------------------------------------------------------\r\n -- Increment\r\n ----------------------------------------------------------------------------\r\n SET @DateActiveStart = DateAdd(Day, 1, @DateActiveEnd);\r\n SET @FutureDate = DateAdd(DAY, @BatchSize - 1, @DateActiveStart); -- Note: subtract 1 from @BatchSize as we include the start date in the batch\r\n SET @DateActiveEnd = CASE WHEN @FutureDate < @DateEnd THEN @FutureDate ELSE @DateEnd END;\r\nEND;\r\n \r\nPRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Completed Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> ' + CONVERT(VARCHAR(20), @DateEnd, 120);<\/pre>\n