Kevin Waterson wrote:
I have been trying this is several ways, currently I have a mess MySQL 4.1.1 PHP as the interface
I have a table of with a date range called seasons. in it I have two date ranges and an amount to be charged
for each day in the range
2004-01-01 00:00:00 2004-06-01 00:00:00 44 2004-06-02 00:00:00 2004-10-31 00:00:00 110
seasonDateFrom seasonDateTo seasonRateWeekly 2004-06-02 00:00:00 2004-10-31 00:00:00 42.86 2004-01-01 00:00:00 2004-06-01 00:00:00 34.29
When I take a booking I have yet another range $bookingDateFrom and $BookingDateTo
I need to get the SUM(seasonRateWeekly) for each day in the booking range.
Currently , and here is the bad bit, I can get it to work if I calculate the number of days in the booking range, then loop through them in php
with foreach and increment a counter
SELECT seasonRateWeekly FROM seasons WHERE DATE_ADD('{$newbookingDateFrom}', INTERVAL $i DAY)
BETWEEN seasonDateFrom AND seasonDateTo
This of course is almost useless as it takes 40 queries for 40 days. Not efficient at all. But I need the individual values, I think, to be able to query across season ranges should a booking range span two, or more, seasons.
As always, any help greatfully recieved Kind regards Kevin
Kevin,
I'm finding your description of the problem just a little confusing. When you say that you have two date ranges in your rates table (seasons), you mean that currently you have just the 2 rows quoted (twice?) above, right? And even though the column is named seasonRateWeekly, it contains daily rates? Also, you say the range start and end are dates, but they appear to be datetimes? The following suggestions are based on my best guess as to what you want:
First, I must say that if there are only two rates, making a table out of them and trying to do this in SQL seems like overkill. Surely it would be simpler to just code them in your application. But I'm guessing that was just an example.
A slightly less easy but more flexible solution, as you're already looping through all the days in the booking range, would be to read in all the season start/end dates and rates (1 query) and do the calculation in your application.
Assuming, however, that your season rates table is just an example, I imagine you might have several seasons, and may wish to have the flexibility to have different rates for certain days (weekends, holidays, etc.). In that case, I would make a rates table with one row for each day of the year. Something like
CREATE TABLE rates (day_of_year DATE, daily_rate DECIMAL(8,2) UNSIGNED)
Fill it with one row for each day of the year and set each day's rate according to the seasonal rate (can be done with one UPDATE statement per season), then set any special days/rates you want. Once you have your daily rates set up, your query becomes simply
SELECT SUM(daily_rate) FROM rates WHERE day_of_year BETWEEN $bookingDateFrom AND $BookingDateTo
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
