I am working on a booking system for vacation rental properties. I have two (simplified) tables properties and propertyAvailability:
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
dayOfTheYear BETWEEN doty1 AND doty2,
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.