Creating Multiple Records from One

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 2000 Forums

SQL Server Development (2000)

Creating Multiple Records from One

Author

Topic

GrantMason
Starting Member

7 Posts

Posted - 2002-05-07 : 09:33:57

Hi,

I have a table that stores details of events which customers can book. Some event are one day long, some more (always concurrent). The table stores EventName, StartDate, NoOfDays. What I want is the SQl for a query that I can use a a kind of diary. ie. If the event last 3 days a record that returns the date, eventname for each day of the event.

Can anyone help (if you understand what I mean?)

Thanks

Grant

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-07 : 11:51:31

Grant, hope this helps.
It does, however, use a cursor. So maybe some-one can suggest a better approach.

create table #Events
(EventName varchar(10),
StartDate datetime,
NoOfDays int)

insert #Events values('Foam Party', '01-15-2002', 1)
insert #Events values('SQL Forum', '06-25-2002', 3)
insert #Events values('MTB Ride', '06-04-2002', 2)

-- Get all one day events
select * into #EventDiary from #Events where NoOfDays = 1

-- Get all multi-day events
DECLARE NoOfDays_cur CURSOR
FOR
SELECT DISTINCT NoOfDays FROM #Events WHERE NoOfDays > 1
OPEN NoOfDays_cur
DECLARE @days int
DECLARE @DaysAdd int
FETCH NEXT FROM NoOfDays_cur INTO @days
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
select @DaysAdd = @days
While @DaysAdd > 0
BEGIN
select @DaysAdd = @DaysAdd - 1
insert into #EventDiary
select EventName, dateadd(dd, @DaysAdd, StartDate) as startday, NoOfDays from #Events where NoofDays =2
END
END
FETCH NEXT FROM NoOfDays_cur INTO @days
END
CLOSE NoOfDays_cur
DEALLOCATE NoOfDays_cur

select * from #EventDiary

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 13:02:55

If you don't want to use a cursor, this should do the trick:

http://www.sqlteam.com/item.asp?ItemID=3332

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources