Project Detail

Description

I need a couple of MySQL5 select queries created for two tables (attached) on an existing holiday villa availability calendar.

The first query is to sort out prices by dates from table availabilitybookingcalendar_prices so it reads:

Price 150 from 2008-07-01 to 2008-07-23
Price 300 from 2008-07-24 to 2008-08-09
Price 600 from 2008-08-10 to 2008-08-28
Price 400 from 2008-08-29 to 2008-09-20
Price 300 from 2008-09-21 to 2008-11-06
Price 150 from 2008-11-07 to 2008-11-26
Price 600 from 2008-11-27 to 2008-12-17 etc.

I need to make a price list for a holiday villa. The pricelist would need to ignore periods where there is no price, show price lists from the current month and order the pricelist by date.

-----------------------------------------------------------------

The 2nd query combines both tables availabilitybookingcalendar_prices and availabilitybookingcalendar_reservations to make a search for available dates below a maximum price.

For example a search for availability between arrival on 14 August 2008 and departure on 21 August 2008 max price 400 would return false because dates are not available, but a search for arrival on 20 August 2008 and departure on 25 August 2008 max price 400 would return true as the dates are free and the price is below 600.

There is a potential problem in that the system has half dates for some of the beginning and ends of the booked period so these days would need to be seen as free.

I use PHP version 5.2.5

MySQL version 5.0.51a-community


Additional files submitted:
price.sql
reservation.sql