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!
Comments