Query to group dates on row that are set per day

[wipeskim]

i have a table with day_date and price each record is displayed as below:

Day_date Price Allotment
2012-05-20 500 2
2012-05-21 500 2
2012-05-22 500 2
2012-05-23 600 4
2012-05-24 600 4
2012-05-25 500 7
2012-05-26 500 7
2012-05-27 700 2
2012-05-28 700 2
what i want to do is display as follow:

season 2012-05-20 to 2012-05-22 price 500 allotment 2
season 2012-05-23 to 2012-05-24 price 600 allotment 4
season 2012-05-25 to 2012-05-26 price 500 allotment 7
season 2012-05-27 to 2012-05-28 price 700 allotment 2
I am trying to do this using the following query below:

SELECT min(day_add) as start_date, max(day_add) as end_date, price, allotment
FROM table GROUP BY price,allotment
With this query i get the following result:

2012-05-20 2012-05-26 500 2

2012-05-23 2012-05-24 600 4

2012-05-27 2012-05-28 700 2
as you see compared what i want to do is that the first record with price=500 should be from 20 to 22 of May 2012 and then on 3rd place should come 25 to 26 may wich the price=500 and allotment=7.

Since i am grouping by price it's cutting the season from 25 to 26 may 2012.

Is it possible to do SELECT to get the results as i want?

[smantscheff]

Your query would be fine if you replace 'day_add' by 'date_date':

mysql> create table d (day_date date, price integer, allotment integer);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into d values
    -> ('2012-05-20','500','2'),
    -> ('2012-05-21','500','2'),
    -> ('2012-05-22','500','2'),
    -> ('2012-05-23','600','4'),
    -> ('2012-05-24','600','4'),
    -> ('2012-05-25','500','7'),
    -> ('2012-05-26','500','7'),
    -> ('2012-05-27','700','2'),
    -> ('2012-05-28','700','2');
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> select min(day_date) as start, max(day_date) as end,  price, allotment
    -> from d
    -> group by price, allotment
    -> ;
+------------+------------+-------+-----------+
| start      | end        | price | allotment |
+------------+------------+-------+-----------+
| 2012-05-20 | 2012-05-22 |   500 |         2 |
| 2012-05-25 | 2012-05-26 |   500 |         7 |
| 2012-05-23 | 2012-05-24 |   600 |         4 |
| 2012-05-27 | 2012-05-28 |   700 |         2 |
+------------+------------+-------+-----------+
4 rows in set (0.00 sec)

[wipeskim]

Sorry my mistake on the season 25 to 26 may 2012 the allotment number should be =2 wich matches also the season 20 to 22 may 2012 , if i use the query you did it will not come out the 4 seasons and only 3, the 25 to 26 may season will not show up. Any other query to do this?

[smantscheff]

Please show the actual input data and your query again. I do not understand your update.

[wipeskim]

Hello Smantscheff,

i will post the table and results a

CREATE TABLE t
    (Day_date date, Price int,allotment int);

INSERT INTO t VALUES
    ('2012-05-20', 500,2),
    ('2012-05-21', 500,2),
    ('2012-05-22', 500,2),
    ('2012-05-23', 600,2),
    ('2012-05-24', 600,2),
    ('2012-05-25', 500,2),
    ('2012-05-26', 500,2),
    ('2012-05-27', 700,6),
    ('2012-05-28', 700,2);

the query i want to do is to list the results as follow:

season start: 2012-05-20 end season: 2012-05-22 price: 500 allotment:2
season start: 2012-05-23 end season: 2012-05-24 price: 600 allotment:2
season start: 2012-05-25 end season: 2012-05-26 price: 500 allotment:2
season start: 2012-05-27 end season: 2012-05-27 price: 500 allotment:2
season start: 2012-05-28 end season: 2012-05-28 price: 500 allotment:2

if you check the days 20 to 22 and 25 to 26 may 2012 the price and allotment values are the same but since there is a season before that totally different this should list, the same applies on the days 27 and 28 since has same price but different allotment as separate season. So the query should be ordered by day and detect when there is new days , price and allotment should group by the start and end date even if after a record with different values comes a record where it had before the same price and allotment such as 21 to 22 may and 25 to 26 may 2012.
So if you use the query below does not show the way i want:

 select min(day_date) as start, max(day_date) as end,  
price, allotment
from t
group by price, allotment

as it will display on where the price is 500 and allotment value=2 says start date is 20 May to 26 May wich is not true as it is overlaping with the other seasons.

[smantscheff]

Now it starts to make sense. There is still an error in your sample. The tuple

('2012-05-27', 700,6)

does not show in your desired results.

An inefficient, but working solution:

select min(day_date), max(day_date), price, allotment
from
(
select t1.*,
(select max(t2.day_date) from t t2 where (t1.price != t2.price or t1.allotment != t2.allotment) and t1.day_date > t2.day_date) as prior_group,
(select min(t3.day_date) from t t3 where (t1.price != t3.price or t1.allotment != t3.allotment) and t1.day_date < t3.day_date) as next_group
from t t1
) tx
group by price,allotment, prior_group, next_group
order by min(day_date)

+---------------+---------------+-------+-----------+
| min(day_date) | max(day_date) | price | allotment |
+---------------+---------------+-------+-----------+
| 2012-05-20    | 2012-05-22    |   500 |         2 |
| 2012-05-23    | 2012-05-24    |   600 |         2 |
| 2012-05-25    | 2012-05-26    |   500 |         2 |
| 2012-05-27    | 2012-05-27    |   700 |         6 |
| 2012-05-28    | 2012-05-28    |   700 |         2 |
+---------------+---------------+-------+-----------+