TSQL - create a variable number of rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

TSQL - create a variable number of rows

Author

Topic

frynturn
Starting Member

2 Posts

Posted - 2008-05-04 : 19:47:20

I have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :)
Thanks


ORIGINAL DATA
EventID  StartDate  EndDate
      1     1/1/08   1/1/08
      2    20/1/08  22/1/08


REQUIRED DATA
EventID  EventDate
      1     1/1/08
      2    20/1/08
      2    21/1/08
      2    22/1/08

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 01:19:36

[code]SELECT t.EventID, DATEADD(d,v.number,t.StartDate) AS EventDate
FROM YourTable t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(d,v.number,t.StartDate) <= t.EndDate[/code]

frynturn
Starting Member

2 Posts

Posted - 2008-05-05 : 18:14:56

Thank you visakh16 - I never knew about the spt_values table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources