Brendan Enrick

Daily Software Development

Using a Numbers Table to Iterate Over Dates

I recently wrote some SQL which would do some work for a single day, but I wanted my code to be able to run once for each day in a date range. Luckily for me, I read Gregg Stark's SQL Blog. He recently posted about how to Iterate Over a List of Dates using a Numbers table in SQL. The SQL he gives is pretty much a cookie cutter solution to the problem.

The basic idea of this trick is to have a cursor which is going to allow you to fetch each of the dates into a variable for use in your code.

You first need to have a Numbers table in your database. This is basically just a table which contains a single number for each row. Use the following short snippet to create your Numbers table.

 
CREATE TABLE dbo.Numbers
(
    Number int IDENTITY(1, 1) PRIMARY KEY
)
GO
 
declare @MaxNumber int
set @MaxNumber = 65535
WHILE 1 = 1
BEGIN
    INSERT INTO dbo.Numbers DEFAULT VALUES
    
    IF scope_identity() = @MaxNumber 
    BEGIN
        BREAK
    END
END
GO

Once you have this numbers table you can follow this cookie cutter solution Gregg created which is actually a very nicely written solution to the problem.

Declare @StartDate datetime
Declare @EndDate datetime
 
-- Note: This StartDate is Exclusive and this EndDate is Inclusive
set @StartDate = '10/1/2007'
set @EndDate = '10/9/2007'
 
declare @CurrentDate datetime
 
-- Create the cursor with the dates using the numbers table
declare datecursor cursor forward_only 
    for Select dateadd(d,Number,@StartDate) from Numbers
          where Number <= datediff(d, @StartDate, @EndDate)
          order by Number
open datecursor
 
-- Loop which will exit when we are out of dates to check
while (1=1)
begin
    fetch next from datecursor into @CurrentDate 
    if @@fetch_status <> 0
        break;
    
    -- This is the code which will run for each date
    select * from some_table where DateRecorded = @CurrentDate
 
end
 
-- Cursor Cleanup
close datecursor
deallocate datecursor

Happy SQL writing!

Loading