gaps in integer ranges

hello, i have quite a challenge on my hands here and would appreciate any help. :confused:

I have a table variable that stores integer ranges representing times of the day:

select * from @.reservations

room date | starttime | endtime
1 2004-12-11 0 1440 (represents an entire day in minutes)
2 2004-12-12 420 1020
3 2004-12-14 200 600
4 2004-12-15 0 200
4 2004-12-15 500 1000

I need to be able to return the minutes that are open for each room. The @.reservations table shows me the times that are blocked.

I'd like to analyze each row and return an integer range representing gaps in the day, where 0-1440 represents an entire day.

Based on the @.reservations table above, I'd like to write something that returns:

room date starttime endtime
2 2004-12-12 0 420
2 2004-12-12 1020 1440
3 2004-12-14 0 200
3 2004-12-14 600 1440
4 2004-12-15 200 500
4 2004-12-15 1000 1440

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! :)in the results, do you also want

1 2004-12-12 0 1440
1 2004-12-13 0 1440
1 2004-12-14 0 1440
1 2004-12-15 0 1440
2 2004-12-13 0 1440
2 2004-12-14 0 1440
2 2004-12-15 0 1440
3 2004-12-11 0 1440
3 2004-12-12 0 1440
3 2004-12-13 0 1440
3 2004-12-15 0 1440
4 2004-12-11 0 1440
4 2004-12-12 0 1440
4 2004-12-13 0 1440
4 2004-12-14 0 1440

or do you just want the available ranges for dates where some part of the date is already booked?|||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

thank you!|||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.