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.
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?)ThanksGrant |
|
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 eventsselect * into #EventDiary from #Events where NoOfDays = 1-- Get all multi-day eventsDECLARE NoOfDays_cur CURSORFOR SELECT DISTINCT NoOfDays FROM #Events WHERE NoOfDays > 1OPEN NoOfDays_curDECLARE @days intDECLARE @DaysAdd intFETCH NEXT FROM NoOfDays_cur INTO @daysWHILE (@@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 @daysENDCLOSE NoOfDays_curDEALLOCATE NoOfDays_curselect * from #EventDiary |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|