Create table variable with a record for each date in a range

[StrikeEagleII]

I am creating data for a chart. Each record in the end recordset needs to have a date and the number of open issues on that date (that I can then dump to excel for a chart).

My strategy is to create a table variable populated with a record for each date within the desired range (an interval of 5 days should give the chart sufficient resolution) and use that as a CTE in a query that counts the number of issues open on each date in that table.

Once I have the dates, creating the actual counts and sending them to excel is easy. I just don't know the most efficient way to create a table variable on the fly that is populated with a record for each date within the desired range (say between 1/1/2010 and 12/31/2010...the table variable would then have the records:
1/1/2010
1/6/2010
1/11/2010
1/16/2010
.
.
.
12/26/2010
12/31/2010

The follow code works and actually performs pretty well, but is it really the most efficient way?

Declare @StartDate datetime = '1/1/2010'
Declare @EndDate datetime = '12/31/2010'
Declare @DateInc int = 5
Declare @dateVar datetime

DECLARE @tblDates table (SelDate DATETIME NOT NULL PRIMARY KEY (SelDate))    

Set @dateVar = @StartDate

While @dateVar <= @EndDate
Begin
	INSERT INTO @tblDates(SelDate) VALUES     (@dateVar)
	set @dateVar = @dateVar + @DateInc
end  

Select d.SelDate from @tblDates d order by d.SelDate

 

[markros]

The best way to populate the Calendar table will be in using Tally (Numbers) table (you can simulate numbers table by using master..spt_values table).

Kendra Little has a very good blog on this topic
Filling in Data Potholes Redux: Tally Tables vs CTEs

PluralSight Learning Library

 

[simian336]

As I often state I like to use a permenant calendar table.

Take a look at...

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi