here's a quick and dirty way to do it:
1) make the seasons table look like this:
dayofyear int
season enum('winter','spring','summer','fall')
rate decimal(5,2)
2) populate 'seasons' with 1-366 for dayofyear and the corresponding
season and daily rate.
3) to get the total rate do a query like:
select sum(seasons.rate) as total,sum(1) as daysbooked from
bookings,seasons where
bookings.id=$id and
seasons.dayofyear BETWEEN DATE_FORMAT(bookingFromDate,"%j") AND
DATE_FORMAT(bookingToDate,"%j")
I don't know how you're charging weekly rates, but you could just
check if daysbooked>=7 and knock a percentage off total.
gl,
- Mark
On Wed, 3 Mar 2004 22:28:20 +1100, Kevin Waterson wrote:
> I am (trying) to make a booking systems.
> Currently I have a table with 3 timestamps that record bookingDate,
> bookingFromDate and bookingToDate
>
> I have another table that records 'seasons'.
> This table contains two timestamps that record
> seasonStartDate and seasonEndDate
> also I have the rates as seasonRateWeekly and seasonRateNightly
>
>
> What I need to do, is INSERT INTO bookings the bookingFromDate and
> bookingToDate, no problem there, but I need to know what rate to
> charge them. and if the booking dates overlap seasons, the
> appropriate rate needs to be applied.
>
> All replies greatfully recieved,
> Kevin
>
>
> --
> ______
> (_____ \
> _____) ) ____ ____ ____ ____
> | ____/ / _ ) / _ | / ___) / _ )
> | | ( (/ / ( ( | |( (___ ( (/ /
> |_| \____) \_||_| \____) \____)
> Kevin Waterson
> Port Macquarie, Australia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
