hierarchical query in Oracle

Question

I am trying to write a query to get continuous subscription date for members, but unable to pull for a scenario like below where end_date overlap for few date ranges for a member, please help to provide the logic/query in oracle.

Data in the Table

ID	Start_date	End_date
12345	07-Aug-15	07-Aug-65
12345	22-Aug-15	01-Jan-16
12345	24-Mar-16	23-Mar-66
12345	06-Jul-16	31-Dec-17
12345	31-Dec-16	31-Dec-41
46628	22-Aug-15	22-Dec-15
46628	01-Jan-16	01-Aug-18
46628	10-Jun-17	31-Dec-18
46628	01-Dec-18	04-Dec-72

Expected Data

ID	Start_date	End_date
12345	07-Aug-15	23-Mar-66
46628	22-Aug-15	22-Dec-15
46628	01-Jan-16	04-Dec-72

Below is the logic i am using, but its not giving expected result, please help.

    SELECT ID, 
        START_DATE, 
        END_DATE 
    FROM (
        SELECT ID,
            CONNECT_BY_ROOT START_DATE START_DATE,
            DAYS_DIFF, 
            END_DATE,
            PREV_END,
            CONNECT_BY_ISLEAF ISLEAF
        FROM ( 
            SELECT ID,
                DAYS_DIFF,PREV_END, START_DATE,END_DATE   
            FROM (   
                SELECT ID,
                    ROUND(START_DATE-PREV_END) DAYS_DIFF,
                    CASE 
                        WHEN START_DATE<=PREV_END THEN PREV_END 
                    END PREV_END, 
                    START_DATE,END_DATE
                FROM ( 
                    SELECT ID,
                        LAG(END_DATE) OVER (PARTITION BY ID ORDER BY START_DATE) PREV_END, 
                        START_DATE,
                        END_DATE  
                    FROM TEST_TABLE A 
                )                  
            )     
        ) 
        CONNECT BY ID= PRIOR ID AND   PREV_END=  PRIOR END_DATE    START WITH PREV_END IS NULL  
    ) WHERE ISLEAF=1; 

Answer 1

From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT *
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY id
         ORDER BY start_date, end_date
         MEASURES
           FIRST(start_date) AS start_date,
           MAX(end_date) AS end_date
         PATTERN (overlapping* final_range)
         DEFINE overlapping AS NEXT(start_date) <= MAX(end_date)
       )

In earlier versions you can use:

SELECT id,
       MIN(dt) AS start_date,
       MAX(dt) AS end_date
FROM   (
  SELECT id,
         dt,
         SUM(group_change) OVER (PARTITION BY id ORDER BY dt)
          AS grp
  FROM   (
    SELECT id,
           dt,
           CASE type * SUM(type) OVER (PARTITION BY id ORDER BY dt, type DESC, ROWNUM)
           WHEN 1
           THEN 1
           ELSE 0
           END AS group_change
    FROM   table_name
           UNPIVOT (dt FOR type IN (start_date AS +1, end_date AS -1))
  )
)
GROUP BY id, grp

Which, for the sample data:

CREATE TABLE table_name (id, start_date, end_date) AS
  SELECT   12345, DATE '2015-08-07', DATE '2065-08-07' FROM DUAL UNION ALL
  SELECT   12345, DATE '2015-08-22', DATE '2016-01-01' FROM DUAL UNION ALL
  SELECT   12345, DATE '2016-03-24', DATE '2066-03-23' FROM DUAL UNION ALL
  SELECT   12345, DATE '2016-07-06', DATE '2017-12-31' FROM DUAL UNION ALL
  SELECT   12345, DATE '2016-12-31', DATE '2041-12-31' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2015-08-22', DATE '2015-12-22' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2016-01-01', DATE '2018-08-01' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2017-06-10', DATE '2018-12-31' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2018-12-01', DATE '2072-12-04' FROM DUAL;

Both output:

ID	START_DATE	END_DATE
12345	2015-08-07 00:00:00	2066-03-23 00:00:00
4662828	2015-08-22 00:00:00	2015-12-22 00:00:00
4662828	2016-01-01 00:00:00	2072-12-04 00:00:00

fiddle

Answer 2

This is one query that could get your expected result

WITH tmp AS
(
    SELECT 12345 AS id,     TO_DATE('07-Aug-15', 'DD/MON/YY') AS Start_date,    TO_DATE('07-Aug-65', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('22-Aug-15', 'DD/MON/YY') AS Start_date,    TO_DATE('01-Jan-16', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('24-Mar-16', 'DD/MON/YY') AS Start_date,    TO_DATE('23-Mar-66', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('06-Jul-16', 'DD/MON/YY') AS Start_date,    TO_DATE('31-Dec-17', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('31-Dec-16', 'DD/MON/YY') AS Start_date,    TO_DATE('31-Dec-41', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    
    SELECT 4662828 AS id,   TO_DATE('22-Aug-15', 'DD/MON/YY') AS Start_date,    TO_DATE('22-Dec-15', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 4662828 AS id,   TO_DATE('01-Jan-16', 'DD/MON/YY') AS Start_date,    TO_DATE('01-Aug-18', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 4662828 AS id,   TO_DATE('10-Jun-17', 'DD/MON/YY') AS Start_date,    TO_DATE('31-Dec-18', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 4662828 AS id,   TO_DATE('01-Dec-18', 'DD/MON/YY') AS Start_date,    TO_DATE('04-Dec-72', 'DD/MON/YY') AS End_date FROM dual 
)
, tmp1 AS
(
    SELECT 
        CONNECT_BY_ROOT t.start_date AS start_date,
        t.id,
        t.start_date AS start_date1,
        t.end_date,
        level AS lvl
    FROM tmp t
    CONNECT BY PRIOR id = id AND PRIOR start_date <> start_date AND PRIOR end_date <> end_date 
        AND PRIOR end_date BETWEEN start_date AND end_date
)
, tmp2 AS 
(
    SELECT *
    FROM tmp1
    WHERE (start_date, id, lvl) IN
        (
            SELECT start_date, id, MAX(lvl)
            FROM tmp1
            GROUP BY start_date, id
        )
)
SELECT id,
    MIN(start_date) AS start_date,
    end_date
FROM tmp2
GROUP BY id, end_date
ORDER BY id, start_date;

ID	START_DATE	END_DATE
12345	07-AUG-15	23-MAR-66
4662828	22-AUG-15	22-DEC-15
4662828	01-JAN-16	04-DEC-72

fiddle