how to find which rows are being overlap while finding overlap b/w the dates

How can we find the between the overlap lap b/w the dates . overlap means when start date and end date are within same range for below example row 1 has no over lap. Row 2to 5 can be considered as one set of over lap as there start date and end are over lap with themselves Row 6 & 7 can be considered as one set of over lap for eg. row 6 & 7 –> start date of row 7 is in same range with respect to end date of row 6 so it becomes an overlap

Once overlap is found then and need to find out min(start date) and max(end date) and want to assign a unique id to each overlap and in the S.NO column should show which rows are overlapped.

Below is the I/p and O/p

Answer

Based on this answer (which contains a detailed explanation of the query), you can change the final part to generate groups of overlapping rows and then aggregate using LISTAGG to get the delimited sno values:

SELECT id,
       MIN( dt ) AS start_date,
       MAX( dt ) AS end_date,
       LISTAGG( CASE value WHEN 1 THEN sno END, ',' )
         WITHIN GROUP ( ORDER BY dt ) AS snos
FROM   (
  SELECT sno,
         id,
         dt,
         value,
         SUM( start_end ) OVER ( ORDER BY dt ASC, value DESC )
           AS grp
  FROM   (
    SELECT sno,
           id,
           dt,
           value,
           CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
           WHEN 1 THEN 1
           WHEN 0 THEN 0
           ELSE NULL
           END AS start_end
    FROM   table_name
    UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
  )
)
GROUP BY id, grp;

Which, for the sample data:

CREATE TABLE table_name ( sno, id, start_date, end_date ) AS
SELECT 1, 1, DATE '2020-10-11', DATE '2020-10-11' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-11-04', DATE '2020-12-11' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2020-11-05', DATE '2020-11-10' FROM DUAL UNION ALL
SELECT 4, 1, DATE '2020-11-06', DATE '2020-11-10' FROM DUAL UNION ALL
SELECT 5, 1, DATE '2020-11-20', DATE '2020-12-20' FROM DUAL UNION ALL
SELECT 6, 1, DATE '2021-01-01', DATE '2021-01-20' FROM DUAL UNION ALL
SELECT 7, 1, DATE '2021-01-01', DATE '2021-03-25' FROM DUAL;

Outputs:

ID | START_DATE          | END_DATE            | SNOS   
-: | :------------------ | :------------------ | :------
 1 | 2020-10-11 00:00:00 | 2020-10-11 00:00:00 | 1      
 1 | 2020-11-04 00:00:00 | 2020-12-20 00:00:00 | 2,3,4,5
 1 | 2021-01-01 00:00:00 | 2021-03-25 00:00:00 | 6,7    

db<>fiddle here

---

From Oracle 12c you can use MATCH_RECOGNIZE for a much simpler query:

SELECT id,
       MIN( start_date ) AS start_date,
       MAX( end_date )   AS end_date,
       LISTAGG( sno, ',' ) WITHIN GROUP ( ORDER BY start_date ASC, END_DATE ASC )
         AS snos
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY start_date ASC, end_date ASC
  MEASURES  MATCH_NUMBER() AS grp
  ALL ROWS PER MATCH
  PATTERN ( OVERLAPPING_DATES* LAST_DATE )
  DEFINE OVERLAPPING_DATES AS NEXT( start_date ) <= MAX( end_date )
)
GROUP BY id, grp

db<>fiddle here