Generate resource free/busy query using SQL Server

Question

In SQL Server 2005 I have a table with the following columns:

Table Reservations:
ID
arrival datetime
departure datetime
reservation_object (FK to objects table)

Now I need to generate a report that shows for a specified period the status of an object. This report will have to look like this:

            6/1/09  6/2/09  6/3/09  6/4/09 ... 
object 1    free    free    busy    busy
object 2    busy    free    busy    busy
...

What is the best way to get this information from SQL server? I'm going to use it in C#.Net.

Answer 1

You may find it easier to pivot the data client side - so ask SQL server for all the appointments that start before the end of the date window you're interested in, but only those that finish after the start of the date window you're interested in.

You can then create a two dimensional array that is logically bookable resource on one dimension, date on the other, with a (potential list of) reservation at each cell -- this would allow you to then drill into details by day without a server round-trip as well...

Answer 2

I decided to use http://www.daypilot.org/scheduler-lite.html.