You know.... there usually more than 1 way to do things. What I am about to suggest may sound strange, but bear with me.
I suggest you add a
Numbers table to your database. With a numbers table, the query becomes much easier to write and allows you more flexibility to create other, more interesting queries. I have a numbers table in every database I work with.
This numbers table should have 2 columns and 1 million rows. It should have a NUM column and a CalendarDate column. You might think.... 1 million rows.... that's huge. You would be wrong. Think of it this way. An int column takes 4 bytes, a DateTime column takes 8 bytes. So, we have 12 bytes per row. We are also going to create a couple indexes on this table, which take up more room.... let's say, another 12 bytes per row, for a total of 24 bytes. Multiply that by 1 million, and the estimated storage space for this table will be 24 megabytes. Pretty modest by today's standards.
By having this table, you will be able to write better queries that execute MUCH faster. This table only needs to be created once, here's how...
Code:
Create Table Numbers(Num Int Identity(1,1) Primary Key Clustered, CalendarDate DateTime)
Declare @i Int
Set @i = 1
While @i <= 1000000
Begin
Insert Into Numbers Default Values
Set @i = @i + 1
End
Update Numbers Set CalendarDate = Num
Create Index Numbers_CalendarDate On Numbers(CalendarDate)
Select * from Numbers
You should expect this to take several minutes to run. This only needs to be done once.
Now that you have this table in your database, you can write some interesting queries, like this....
Code:
Select DateName(Weekday, DateAdd(Day, Num, 0)) As [Weekday],
DateAdd(Day, Num, 0) + Convert(Float, ScheduledStart) - Convert(Int, ScheduledStart) As ReserveDateStart,
DateAdd(Day, Num, 0) + Convert(Float, ScheduledEnd) - Convert(Int, ScheduledEnd) As ReserveDateStart,
EventName
From Event
Inner Join Numbers
On Numbers.CalendarDate >= Event.ScheduledStart -1
And Numbers.CalendarDate <= Event.ScheduledEnd
Where [!]EventId = 7[/!]
The query above would return multiple rows for each event (if it spans multiple days).
Or, you could write a query that shows all the events for a single day, like this...
Code:
Select DateName(Weekday, DateAdd(Day, Num, 0)) As [Weekday],
DateAdd(Day, Num, 0) + Convert(Float, ScheduledStart) - Convert(Int, ScheduledStart) As ReserveDateStart,
DateAdd(Day, Num, 0) + Convert(Float, ScheduledEnd) - Convert(Int, ScheduledEnd) As ReserveDateStart,
EventName
From Event
Inner Join Numbers
On Numbers.CalendarDate >= Event.ScheduledStart -1
And Numbers.CalendarDate <= Event.ScheduledEnd
Where [!]Numbers.CalendarDate = '20080827'[/!]
Or for the whole month...
Code:
Select DateName(Weekday, DateAdd(Day, Num, 0)) As [Weekday],
DateAdd(Day, Num, 0) + Convert(Float, ScheduledStart) - Convert(Int, ScheduledStart) As ReserveDateStart,
DateAdd(Day, Num, 0) + Convert(Float, ScheduledEnd) - Convert(Int, ScheduledEnd) As ReserveDateStart,
EventName
From Event
Inner Join Numbers
On Numbers.CalendarDate >= Event.ScheduledStart -1
And Numbers.CalendarDate <= Event.ScheduledEnd
Where [!]Numbers.CalendarDate <= '20080801'
And Numbers.CalendarDate < '20080901'[/!]
Or the whole year. The whole week. Whatever. By using a numbers table, there's no looping, so you can show multiple days, multiple events, or both.
Make sense?
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom