I don't think you can do it in a single SQL statement. At least not an efficient one.
How about pull the list of reservations that overlap the desired dates, and then creating an array of dates (from arrival to departure), and then loop over the recordset and "remove" dates from the array that are already taken. When you're done, whatever dates are left are available. cheers, barneyb On 7/28/05, Doug Bedient <[EMAIL PROTECTED]> wrote: > I have 2 tables, one lists properties and the other contains arrival dates > and number of nights for those properties. The common variable is 'unitCode'. > The search parameters provide an arrival date and departure date. > > My question. How would you use the arrival date/number of nights to locate > availability between two search dates. > > <CFQUERY NAME="Check" DATASOURCE="#db#"> > SELECT DISTINCT property_ID FROM Property P > LEFT OUTER JOIN propertyCalendar PC ON P.unitCode = PC.unitCode > > WHERE ??? > > </CFQUERY> > > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213202 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
