Insert multiple rows for a range of dates

[RRinTetons]

When I set up an application for a new season I need to create multiple rows for a range of dates. Given a start date and an end date, what is an elegant way to create a row in a table for each date (inclusive), without a looping or recursive construct?

ScheduleDate
------------
ID_Date INT (PK, Identity}
ScheduleDate DATE
Comment NVARCHAR(255)

For 11/1/2011 through 4/15/2012, insert one row for each date.

-
Richard Ray
Jackson Hole Mountain Resort

 

[gmmastros]

There is a clever method for doing this that is very fast, but it requires that you have a numbers table in your database. Do you have one? A numbers table is simply a table that has x number of rows, with one column. The data looks like: 1,2,3,4,5, etc...



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RTag]

I am using a table function which returns table with all dates in a period. In some cases this can be faster than having a table with hardcoded values.

You can find the function and sample how to use here:

http://r-tag.com/PivotSample.aspx

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[RRinTetons]

George -

I can certainly add a numbers table. Given one of those, how does it go?

RTag -

In this case the date table is an anchor table for many different knds of schedules and logs. The user starts there and chooses what kind of data to look at for that date. That argues foe static data as best I can see.

-
Richard Ray
Jackson Hole Mountain Resort

 

[gmmastros]

You can create a numbers table that has 10,000 rows with this code:

Create Table dbo.Numbers (Num Int Identity(1,1) Primary Key)
go
Set NoCount ON
Insert Into Numbers Default Values
go 10000

The "go 10000" is specific to SQL Server Management Studio, so you need to run the code there.

Then, your query to insert all the dates would be:

Insert 
Into    ScheduleData(ScheduleDate)
Select	DateAdd(Day, Num-1, '20111101')
From	Numbers
Where	Num <= DateDiff(Day, '20111101','20120415') + 1

Notice the Num-1 and the + 1 on the end. This is meant to accommodate the fact that the numbers table starts at one instead of zero. No biggie, but I thought it import to explain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

RTag

I added your function to a DB I have on my local computer. I then ran the following code.

Declare @Start DateTime, @Temp DateTime

Set @Start = GetDate()
Select @Temp = DateValue From [dbo].[fnGetDatesInPeriod]('19700323','20120415')
Select 'Date Function', DateDiff(Millisecond, @Start, GetDate())

Set @Start = GetDate()
Select	@Temp = DateAdd(Day, Num-1, '19700323')
From	Numbers
Where	Num <= DateDiff(Day, '19700323','20120415') + 1
Select  'Numbers Table', DateDiff(Millisecond, @Start, GetDate())

I needed to increase the range of dates and also add to the numbers table so that I could see a significant difference in performance.

When I run the code above, I am getting the numbers table executing in 6 milliseconds and the table function runs in approximate 400 milliseconds. So... I am wondering under what conditions will the table function outperform a hard coded numbers table.

For the record, I am selecting in to a scalar variable so that I am not taking in to account the time it takes to draw the to the screen. I ran the code multiple times and also switched the order in which I ran it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RTag]

Well, if you want to use hardcoded dates you can still use the function to generate them:

INSERT INTO <your table>
SELECT [CurrentDate] FROM dbo.fnDatesInPeriod('xx/xx/xxxx','yy/yy/yyyy')


As I said the function will return a table with dates between 'xx/xx/xxxx' and 'yy/yy/yyyy'

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[RTag]

This is the function:

CREATE FUNCTION [dbo].[fnGetDatesInPeriod]
(
@FromDate DATE,
@ToDate DATE
)
RETURNS @DateTable TABLE (DateValue DATETIME)
AS
BEGIN
WITH DatesTable(CurrentDate) AS
(
SELECT @FromDate AS DATETIME
UNION ALL
SELECT DATEADD(DAY, 1, CurrentDate) FROM DatesTable WHERE CurrentDate < @ToDate
)

INSERT INTO @DateTable (DateValue) SELECT CurrentDate FROM DatesTable OPTION(MAXRECURSION 0)
RETURN;
END

--------------------------------------------------
here is a sample how to use it:
SELECT DateValue from [dbo].[fnGetDatesInPeriod]('1/1/2012','1/1/2013')

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[RRinTetons]

I'll let you guys go at it over the milliseconds. As I do this once and year for a couple of hundred dates I'm happy.

Thanks to both!

-
Richard Ray
Jackson Hole Mountain Resort

 

[RPOC]

i was thinking about this and realized all i really need are calculated numbers for each date.
I was thinking based off your info to try the following.
1. have the user enter a start date and and end date .
2. figure out the number of days with a date diff function, (add 1 etc)
3. build an array for each date so take the first date and use a for or a do while loop to create an array that has each date in it.
4. once i have the date set up formulas to look at the table for certain fields. do calculations and add those calculations into the array based.
5. then do another formula to spit out the data or show the data after the calculations.

I have not done array's that much in crystal. are you familiar with the best way to do this would be ?

thanks for you help so far.