Group overlapping time slots in Oracle

Question

There is a set of data columns date_fromand date_toin Oracle 10g (or, more likely, simply date_frombecause the length is constant).

I need to collapse overlapping intervals, for example. these lines:

date_from  date_to
2015-01-01 2015-01-10
2015-01-03 2015-01-11

necessary:

2015-01-01 2015-01-11

I wonder if there is a way to query this neatly at a time without using cursors ... my query is huge and still doesn't ruin everything in extreme cases. Maybe there is some SQL extension in Oracle 10g for such problems that I can use?

Answer 1

WITH data (date_from, date_to) AS (
    SELECT DATE'2015-01-01', DATE'2015-01-10' FROM DUAL UNION ALL
    SELECT DATE'2015-01-03', DATE'2015-01-11' FROM DUAL
)
SELECT
    min(date_from) date_from, max(date_to) date_to 
FROM (
    SELECT
        date_from, date_to,
        sum(merge) OVER (ORDER BY date_from) group_id
    FROM (
        SELECT
            date_from, date_to,
            case when date_from <= lag(date_to) OVER (ORDER BY date_from)
                 THEN 0
                 ELSE 1
            end as merge
        FROM data)
    ) intervals
GROUP BY group_id
ORDER BY min(intervals.date_from);