Bonaparte Bonaparte - 1 year ago 109
SQL Question

How to combine overlapping date ranges in MySQL?

I have a table „lessons_holidays“. It can contain several holiday date ranges i.e.

"holiday_begin" "holiday_end"
2016-06-15 2016-06-15
2016-06-12 2016-06-16
2016-06-15 2016-06-19
2016-06-29 2016-06-29

I would like to combine each entry if the date ranges overlap. And I need an output like this:

"holiday_begin" "holiday_end"
2016-06-12 2016-06-19
2016-06-29 2016-06-29

SQL: The following sql-statement loads all rows. Now I am stuck.

SELECT lh1.holiday_begin, lh1.holiday_end
FROM local_lessons_holidays lh1
WHERE lh1.holiday_impact = '1' AND
(DATE_FORMAT(lh1.holiday_begin, '%Y-%m') <= '2016-06' AND DATE_FORMAT(lh1.holiday_end, '%Y-%m') >= '2016-06') AND
lh1.uid = '1'

Answer Source

This is a hard problem, made harder because you are using MySQL. Here is an idea. Find the beginning date of all holidays in each group. Then a cumulative sum of the flag for the "beginning" handles the groups. The rest is just aggregation.

The following should do what you want, assuming you have no duplicate records:

select min(holiday_begin) as holiday_begin, max(holiday_end) as holiday_end
from (select lh.*, (@grp := @grp + group_flag) as grp
      from (select lh.*,
                   (case when not exists (select 1
                                          from lessons_holidays lh2
                                          where lh2.holiday_begin <= lh.holiday_end and
                                                lh2.holiday_end > lh.holiday_begin and
                                                (lh2.holiday_begin <> lh.holiday_begin or
                                                 lh2.holiday_end < lh.holiday_end)

                         then 1 else 0
                    end) as group_flag
            from lessons_holidays lh
           ) lh cross join
           (select @grp := 0) params
      order by holiday_begin, holiday_end
    ) lh
group by grp;

If you have duplicates, just use select distinct on the innermost references to the table.

Here is a SQL Fiddle.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download