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?