This result represents the times in minutes that are available.
I have no clue how to do this without using a numbers table and checking each minute in each day for each row in the table. Id like to not do that because of sheer performance reasons. There is a possiblity that I will have hundreds of rows in the @reservations table.
I was hoping someone could provide some insight as to how to approach this. Thank you ahead of time!
I want the available ranges for the dates where some part of the date is already booked
and I just thought that I will also need to know the available times if there is nothing booked in the room...yikes...
so if I have no row in my @reservations table for room 1 on 2004-12-21 then I would want to return
1 2004-12-21 0 1440
Hmmm...would something like this work for that scenario??
I have a stored procedure that going to give me these results. I am writing a search. In the stored procedure I pass in the start date of the search and the end date of the search. So i could potentially, check that if there are no rows in the @reservations table for that date and room, i would need to return 0-1440
The best way I have found to handle this functionality it to create a table in your database that consists of nothing but incrementing value from zero to ....well, whatever the highest number you end up needing.
By supplying a starting date to a SELECT query based upon this table, you can use the values to create a list of time intervals of whatever duration you choose. From this dataset, left join your Reservations table and return only the records where no corresponding Reservation exists. Voila! Your resultset shows all the unbooked times.
If it's not practically useful, then it's practically useless.