Creating Record for Each Date in a Range

[Rob999]

Hi everyone. I've done this before with a Cursor, but wonder if there is a straightforward way to do it using SQL set operations (no cursor).

I have the following 3 fields withing the same table:

ACCT ARRIVE DEPART

Account is numeric, arrive and depart are dates. I'd like to create a record in a new table for each day of the trip. For example, if #123 arrived on the 4th and left on the 6th I'd like to add the following 3 records to the new table:

ACCT DATE
123 4/4/03
123 4/5/03
123 4/6/03

Any straight SQL that would accomplish this sans cursor? Thanks.

 

[tlbroadbent]

Here is one method sans cursor that you could use.

Declare @dt datatime, @acct int

Set @dt='4/4/03'
Set @acct=123

While @dt<='4/6/03'
Begin
Insert YourTable Values(@acct, @dt)
Set @dt = @dt +1
End If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Rob999]

Thanks tl, but what I'm looking at is tens of thousands of records in the original table, which I believe would make the use of parameters less efficient. Sorry I didn't specify this.

 

[swampBoogie]

insert into newTab
select acct, dateadd(day,c - 1,depart)
from oldTab
join (select 1 union select 2 union select 3
union select 4 union select 5 union select 6
union select 7 union select 8 union select 9) dt(c)
on datediff(day,arrive,depart) + 1 <= c

Untested. If the journey can last more than 9 days you can extend the union or create a table with a suitable amount of integer values.

 

[Rob999]

another good one! Yes, the trip can last many more days (some, due to unsettled issues, are years old [not my fault - just my problem...]).

But I think you guys have given me a good start.