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