Property Availability Search

[daddio2005]

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.

 

[PHV]

WHERE [start date]<=PropEnd AND ([start date]+[length])>=PropStart

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daddio2005]

Thanks for your reply but , I do not think this will work. Doesnt this look for the time scale to fit WITHIN the booking times??

I need it to look OUTSIDE the booking times, bearing in mind that the latest booking for a property may be 12/12/05 til 18/12/05 so we know it is free from the 18/12/05 until the next booking startdate. BUT what if there is no next booking start date???? thats the problem I am having.

Cheers.

 

[PHV]

Like this ?
WHERE [start date]>PropEnd OR ([start date]+[length])<PropStart

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[daddio2005]

I do not think it is that simple as there maybe 15 entries for bookings for any one property and SOME of them are bound to conform to the statement you have suggested and therefore return the property ID. But the others that didnt match, contradict the availability!