I am attempting to formulate a query that will tell me if there are any
single-day gaps in a series of records in a reservation table:

Here is the reservation table
-----------------------------
id             int
startdate   smalldatetime
enddate    smalldatetime
guestid      int

Assuming the table has the following data:

1,3/3/2005,3/6/2005,222
2,3/9/2005,3/10/2005,222
3,4/1/2005,4/5/2005,222

I wish to be able to query the table to see if there is at least 1 free day
anywhere in the span 3/5/2005 to 3/8/2005, but so far I haven't been able
to. At first I thought I could SUM the number of days that each reservation
record represented (the first record represents 4 days, the 2nd record has 2
days, the 3rd record has 5 days) and then compare that with the span of days
I am searching, but that would not work since the first record actual starts
2 days before my search range so would throw off the calculation.

Any thoughts on how I could write a query that would tell me if there was
any single day free between 3/5/2005 and 3/8/2005 given the data above?

Thanks experts!

Build a Calendar table, then use it for this and all of your other
temporal queries.

SELECT C1.cal_date
  FROM Calendars AS C1
WHERE NOT EXISTS
  (SELECT *
      FROM Reservations AS R1
  WHERE C1.cal_date BETWEEN R1.start_date AND R1.end_date);

Great idea! Thanks so much!

Mark

Show quoteHide quote

Get a copy of any of my books, which improve your  SQL and my ability
andmy ability to live indoors :)

Shameless, shameless :)

Show quoteHide quote

How do I get Identity value of a record a've just inserted?
Change a stored proc to a function
Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000