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
That sounds like a job for your application so I would just do a few tests on the dates you received from the user before you try to insert a booking record in your database.
like (this is PHP, I don't know what you use but the logic is the same anyway)
if (($s = season($startdate)) == ($e=season($enddate))) {
if interval($startdate,$enddate,"days") > 7) {
$rate = $weekly[$s];
}
else {
$rate = $nightly[$s];
}
} else {
//it straddles two seasons... do something
}then INSERT into bookings .......
I assume bookingDate is
like today's date, and FromDate and ToDates are the date the facility
is requested... You need to define what you want to do for a weekly booking
that straddles seasons, use the higher rate or the lower rate or average them or something
Also just to make processing easier later on you might want to define a flag to indicate whether they are getting a weekly or nightly bill
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
