CREATE TABLE X (MRN VARCHAR2(100), START_DATE DATE, END_DATE DATE, NEXT_DATE DATE); 
Insert into BIGDASC.X
   (MRN , START_DATE, MOD_END_DATE, NEXT_DATE)
 Values
   ('236046', TO_DATE('11/09/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/25/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into BIGDASC.X
   (MRN , START_DATE, MOD_END_DATE, NEXT_DATE)
 Values
   ('236046', TO_DATE('04/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/25/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/25/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into BIGDASC.X
   (MEDICAL_RECORD_NUMBER, START_DATE, MOD_END_DATE, NEXT_DATE)
 Values
   ('236046', TO_DATE('05/25/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/18/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/18/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into BIGDASC.X
   (MRN , START_DATE, MOD_END_DATE, NEXT_DATE)
 Values
   ('236046', TO_DATE('10/18/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/18/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/18/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into BIGDASC.X
   (MEDICAL_RECORD_NUMBER, START_DATE, MOD_END_DATE, NEXT_DATE)
 Values
   ('236046', TO_DATE('10/18/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into BIGDASC.X
   (MRN , START_DATE, MOD_END_DATE, NEXT_DATE)
 Values
   ('236046', TO_DATE('12/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into BIGDASC.X
   (MRN , START_DATE, MOD_END_DATE)
 Values
   ('236046', TO_DATE('12/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

The o/p should be

MRN        START_DATE          END_DATE
236046      11/9/2010            1/9/2012
236046      12/12/2012           1/8/2014

Procedure to get the output:
1) Loop through all the records for a mrn
2) Get the start date, mod_end_date, next_date for a mrn ordered by start date, mod_end_date for a mrn.
3) Store the 1st record into variables
4) See if next_date - mod_end_date > 1
5) If NOT, preserve the start date.
6) Loop until u find the record where next_date - mod_end_date > 1
7) Once u find that record, store the 1st record start date & mod_end_date of the current record. In this case 11/9/2010 - 1/9/2012...
8) Get the next record where the start date will be the new start date .
9) Repeat the whole process.

declare
  V_SAMPLE_NAME VARCHAR2(100);
  V_START_DATE DATE;
  V_END_DATE DATE;
  V_NXT_DATE DATE;
 cursor C_ALL_PATIENTS is
  Select distinct MRN from x;
 
 cursor C_GENO (p_mrn varchar2) is
  Select distinct MRN , to_date(start_date,'mm/dd/yyyy') start_date, mod_end_date, to_date(next_date,'mm/dd/yyyy')  next_date  from x
   where MRN = p_mrn 
   order by 1,2,3;

 TYPE V_TT IS TABLE OF C_GENO%ROWTYPE INDEX BY PLS_INTEGER;
   L_TT V_TT;
    
begin
  FOR L1 IN C_ALL_PATIENTS
   LOOP
    V_SAMPLE_NAME := L1.MRN ;
    
   OPEN C_GENO(L1.MRN );
    LOOP
      FETCH C_GENO BULK COLLECT INTO L_TT LIMIT 100;
       FOR indx IN 1 .. L_TT.COUNT
        LOOP
          V_START_DATE :=  L_TT(indx).START_DATE;
          V_END_DATE   :=  L_TT(indx).MOD_END_DATE;
          V_NXT_DATE   :=  L_TT(indx).NEXT_DATE;

          IF  L_TT(indx).NEXT_DATE -  L_TT(indx).MOD_END_DATE > 1
           THEN 
              -- LOGIC
          END IF;
              
         END LOOP;
      EXIT WHEN L_TT.COUNT = 0;
     
    END LOOP;
CLOSE C_GENO;    
   END LOOP;
COMMIT;   
END; 

with t as (
           select  x.*,
                   sum(
                       case
                         when next_date - end_date > 1 then 1
                         else 0
                       end
                      ) over (
                              partition by mrn
                              order by start_date,
                                       end_date,
                                       next_date
                             ) grp
             from  x
          )
select  mrn,
        min(start_date) start_date,
        max(end_date) end_date
  from  t
  group by mrn,
           grp
  order by mrn,
           grp
/

MRN        START_DAT END_DATE
---------- --------- ---------
236046     09-NOV-10 18-OCT-11
236046     12-DEC-11 08-JAN-14

SCOTT@pdborcl12 >

MRN        START_DAT END_DATE
---------- --------- ---------
236046     09-NOV-10 09-JAN-12  -- the code gives  end_date as 18-OCT-11
236046     12-DEC-12 08-JAN-14  -- the code gives  start_date as 12-DEC-11

with t1 as (
            select  x.*,
                    case
                      when lag(next_date - end_date)
                           over (
                                 partition by mrn
                                 order by start_date,
                                          end_date,
                                          next_date
                                ) > 1 then 1
                      else 0
                    end start_of_group
              from  x
           ),
     t2 as (
            select  t1.*,
                    sum(
                        start_of_group
                       ) over (
                               partition by mrn
                               order by start_date,
                                        end_date,
                                        next_date
                              ) grp
              from  t1
           )
select  mrn,
        min(start_date) start_date,
        max(end_date) end_date
  from  t2
  group by mrn,
           grp
  order by mrn,
           grp
/

MRN        START_DAT END_DATE
---------- --------- ---------
236046     09-NOV-10 09-JAN-12
236046     12-DEC-12 08-JAN-14

SCOTT@pdborcl12 >