CODEWITHSUNDEEP
phpmysql
user1361389
user1361389

Reputation: 55

group season dates with weekdays

I have a mysql table that stores all the days from a date range, the user types a starting and end date and and a price and I store each day and price as row for that specific season and that's the only information i have. This date range can be applies to specific weekdays.

What I want to do is grab all the days and group by season and weekdays.

Example:
i have a date range from 1 to 30/april 2013 for monday to friday=100€
and 1 to 30 april 2013 for saturday to sunday 80€
and 1 may to 31 may for monday - friday = 200€
and 1 may to 31 may for Saturday - Sunday = 180€

I tried to use php to group this but then it's missing the correct dates

The database looks like this:

Day         |  Price
2013-04-01  | 100€
2013-04-02  | 100€
2013-04-03  | 100€
2013-04-04  | 100€
2013-04-05  | 100€
2013-04-06  | 80€
2013-04-07  | 80€
2013-04-08  | 100€
2013-04-09  | 100€
2013-04-10  | 100€
...

Is there a way to use PHP to group this as season with the weekdays?

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You are trying to group things. You need to get the week day and a name for the group.

The approach is to find a "name" for the consecutive values. This "name" is the date in the future where the price is difference from the current price, on either the weekday or weekend (if I understand the question correctly).

Once you have the grouping, just use group by to get the beginning and end of the periods:

select min(day) as startday, max(day) as endday, price
from (select t.*,
             (select day
              from t t2
              where (case when weekday(t2.day) bewteen 0 and 4 then 'Week' else 'Weekend' end) = 
                    t.period and
                    t2.day > t.day and
                   t2.price <> t.price
             ) as grouping
      from (select t.*, (case when weekday(day) bewteen 0 and 4 then 'Week' else 'Weekend' end) as period
            from t
           ) t
     ) t
 group by grouping, price

I haven't tested this SQL, so there might be syntax errors.

Upvotes: 0

Related Questions