0

Hi All,
I am working on a booking system for vacation rental properties. I have two (simplified) tables properties and propertyAvailability:

properties:-
propID
propName

propertyAvailability:-
propID
dayOfTheYear
availabilityStatus

Rather than use dates for the bookings I convert everything to dayOfTheYear (using ColdFusion) and each DOTY for each property has a status of either 0 for available or 1 for booked.

I am trying to search availability and pull out properties that have full availability for a given period. In pseudo-code it goes something like this:

SELECT propID FROM propertyAvailability
WHERE
dayOfTheYear BETWEEN doty1 AND doty2,
AND
availabilityStatus FOR ALL OF THOSE RECORDS IS 0
GROUP BY propID;

I have been racking my brain but I can't come up with an efficient way to implement this query. I can get the result I need but I am using four queries to get there and I strongly suspect it could be done a whole lot more efficiently and elegantly.

Thanks on advance for any help.
Paul.

2
Contributors
2
Replies
6
Views
6 Years
Discussion Span
Last Post by FlyByNight73
0

Hello,

Off the top of my head how about if you sum the availability status and select only those where the sum is 0.

0

Hello,

Off the top of my head how about if you sum the availability status and select only those where the sum is 0.

Great idea, thank you. Here's what I ended up with:

SELECT propID, SUM(availabilityStatus) AS sumAvail
FROM propertyAvailability
WHERE dayOfTheYear BETWEEN 30 AND 37
GROUP BY propRef
HAVING sumAvail = 0;

Seems to work but I'll be putting it through it's paces just to be sure. I am a real novice at SQL but the more I use it the more I see the potential power of it.

Thanks again,
Paul.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.