Search MS SQL DB for available dates

Nico2011 used Ask the Experts™
Hello Experts,

I'm working on a booking engine and would like to know if anyone knows the best way to search a db for availability.  For example, a customer could search for July 1st 2012 +/-7 days, for a 7 day stay, so the query needs to check that there is no booking, either between the June 24th to July 1st, or June 25th to July 2nd, or June 26th to July 3rd etc., right up to July 7th to July 14th.

My DB at the moment has start and end dates, but I'm simply not sure of the best way to do this...!

Please help!  Thanks :-)
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013
I would create a booking transaction table linked to a booking header like an invoice/invoice header.  Then when you want to check for an availability, you just search a view made up of the Booking Header and Booking Transaction and check for a collision.

ID - int
Name - varchar
Price - numeric

ID - int
ContactID - int
Status - int
InventoryID - int

tBookingTrans (one record for each date)
ID - int
BookingID - int - linked to BookingHeader.ID
Date - date
Software Engineer

select *
from yourtable
where not ( date between yourdate and dateadd(DAY, 7, yourdate))


Both work - thank you.  Ewangoya's solution is easier for me to implement as the tables already exist and showed me how to avoid the 'collisions'... all help much appreciated!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial