I have a table „lessons_holidays“. It can contain several holiday date ranges i.e.
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'
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.