Rod,
I hope I've got what you're trying to do.
You have a table with a record for each day and columns rate1 and rate2 -
You are trying to collapse the single days into a range where the rates are
the same?
So if I added column titles to your sample report:
Start End rate1 rate2
> 2002-05-01 | 2002-05-03 | 60 | 70
> 2002-05-04 | 2002-05-05 | 100 | 120
> 2002-05-06 | 2002-05-09 | 80 | 100
> 2002-05-10 | 120 | 140
> 2002-05-11 | 2002-05-14 | 60 | 70
> 2002-05-15 | 2002-05-18 | 40 | 60
Now one problem I notice right off is line 4 is missing one column (perhaps
because there is only one day where these rates were in effect) - you cannot
do this in SQL. SQL insists on very "square" results, that is a SELECT
statement will return the same number of columns in each row, some may be
NULL but each row will have the same number. In your case it's easy to put
the same date in as Start and End.
The more interesting problem is you can't really use GROUP BY for what you
seem to want to do. If you tried:
SELECT min(ratedate), max(ratedate), rate1, rate2
FROM rates
GROUP BY rate1, rate2
It would not work because the rates would get grouped together _anywhere_
they are the same, not just where the ratedate are contiguous. In your
example data 5/1 - 5/3 would be grouped with 5/11 - 5/14 and the above SQL
would return:
Start End rate1 rate2
2002-05-01 | 2002-05-14 | 60 | 70
I think the solution is not in SQL, it's in PHP.
Your select would be:
SELECT ratedate, rate1, rate2
FROM rates
WHERE <whatever you want here>
ORDER BY ratedate
And you assign this to $result
/* set the report titles */
$start = 'Start';
$end = 'End';
$rate1 = 'Rate 1';
$rate2 = 'Rate 2';
while( odbc_fetch_row( $result ))
{
$newRate1 = odbc_result( $result , 2 );
$newRate2 = odbc_result( $result , 3 );
if (($rate1 != $newRate1) || ($rate2 != $newRate2 )) /* rates changed*/
{
print "$start | $end | $rate1 | $rate2 \n"; /* print old rates */
$start = odbc_result( $result , 1 );
$end = odbc_result( $result , 1 );
$rate1 = odbc_result( $result , 2 );
$rate2 = odbc_result( $result , 3 );
} else {
$end = odbc_result( $result , 1 ); /* no rate change - $end gets
older */
}
}
print "$start | $end | $rate1 | $rate2 \n"; /* print last row */
There are perhaps better ways to do this but you see that the variables
$start, $end, $rate1 and $rate2 are printed whenever the rates change then
set to the new values, $end gets set to the new 'ratedate' each time the
rates do not change so when it is printed it will be the last day of the old
rates.
You could also add some code to only have one date column if $start == $end
if you wanted to do this - PHP couldn't care less about 'square' results.
Good Luck,
Frank
On 2/19/02 6:16 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
> From: "Rod Wilkinson" <[EMAIL PROTECTED]>
> Date: Wed, 20 Feb 2002 08:09:46 +0700
> To: [EMAIL PROTECTED]
> Subject: Problems with creating a report
>
> I've been struggling with creating a rate report based on data extracted
> from a
> Mysql table. I had written a piece of PHP code for creating the type of
> report I needed but it has turned out buggy and prone to errors. I then
> tried grouping the rates data in an sql statement but this omits lines and
> leaves my
> dates out of wack. Would someone be able to point me in the right direction
> with how the following table could be created with the below data.
>
> Thanks for any suggestions on how to go about this.
> Rod
>
> 2002-05-01 | 2002-05-03 | 60 | 70
> 2002-05-04 | 2002-05-05 | 100 | 120
> 2002-05-06 | 2002-05-09 | 80 | 100
> 2002-05-10 | 120 | 140
> 2002-05-11 | 2002-05-14 | 60 | 70
> 2002-05-15 | 2002-05-18 | 40 | 60
>
> CREATE TABLE rates (
> id int(11) DEFAULT '0' NOT NULL auto_increment,
> ratedate date DEFAULT '0000-00-00' NOT NULL,
> rate1 smallint(6) DEFAULT '0' NOT NULL,
> rate2 mediumint(9) DEFAULT '0' NOT NULL,
> PRIMARY KEY (id)
> );
> INSERT INTO rates VALUES ( '1', '2002-05-01', '60', '70');
> INSERT INTO rates VALUES ( '2', '2002-05-02', '60', '70');
> INSERT INTO rates VALUES ( '3', '2002-05-03', '60', '70');
> INSERT INTO rates VALUES ( '4', '2002-05-04', '100', '120');
> INSERT INTO rates VALUES ( '5', '2002-05-05', '100', '120');
> INSERT INTO rates VALUES ( '6', '2002-05-06', '80', '100');
> INSERT INTO rates VALUES ( '7', '2002-05-07', '80', '100');
> INSERT INTO rates VALUES ( '8', '2002-05-08', '80', '100');
> INSERT INTO rates VALUES ( '9', '2002-05-09', '80', '100');
> INSERT INTO rates VALUES ( '10', '2002-05-10', '120', '140');
> INSERT INTO rates VALUES ( '11', '2002-05-11', '60', '70');
> INSERT INTO rates VALUES ( '12', '2002-05-12', '60', '70');
> INSERT INTO rates VALUES ( '13', '2002-02-13', '60', '70');
> INSERT INTO rates VALUES ( '14', '2002-05-14', '60', '70');
> INSERT INTO rates VALUES ( '15', '2002-05-15', '40', '60');
> INSERT INTO rates VALUES ( '16', '2002-05-16', '40', '60');
> INSERT INTO rates VALUES ( '17', '2002-05-17', '40', '60');
> INSERT INTO rates VALUES ( '18', '2002-05-18', '40', '60');
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
