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!
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using