Availability Search - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Availability Search

Author

Topic

daddio2005
Starting Member

4 Posts

Posted - 2005-12-08 : 12:53:46

I need to be able to search through a database of property bookings and retreive all properties that are available within the date range specified.

For example:

Arrive: 7 Dec 2005 for 7 Nights.

So availability of a property needs to be: 7 Dec - 14 Dec

I assume I will have a table of property bookings as follows:

BOOKINGS TABLE
PropID PropStart PropEnd PropLength
3 03/12/05 13/12/05 10
5 03/12/05 13/12/05 10
3 15/12/05 28/12/05 13
7 01/01/06 15/01/06 14

Obviously I have another table of property details which relate to the PropID in the above bookings table.

PROPERTY TABLE
PropID PropName
3 My House
5 His House
7 Her House

I want a search box which specifies start date and length (can work out end date from that if required), when submitted returns the details of properties available for this length of time.

I think this will be a tricky one to execute!

Any help on this would be most appreciated.

DrewBurlingame
Starting Member

49 Posts

Posted - 2005-12-08 : 14:50:09

I didn't test this, but it seems pretty straight forward

SELECT
p.PropID,
p.PropName
FROM
BOOKINGS b
INNER JOIN PROPERTY p ON b.PropID = p.PropID
WHERE
b.PropStart < @ArriveDate
AND b.PropEnd > @DepartDate --or DATEADD(d,1,@LengthOfStay)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources