| Author |
 Topic  |
|
|
GrantMason
Starting Member
7 Posts |
 Posted - 05/07/2002 : 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
United Kingdom
616 Posts |
Posted - 05/07/2002 : 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
USA
15732 Posts |
|
| |
Topic |
|
|
|
Creating Multiple Records from One
