Booking Calendar Availability Query

[FlyByNight73]

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.

[rch1231]

Hello,

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

[FlyByNight73]

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.