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