Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE	11.2.0.2.0	Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


Alter session set nls_date_format = 'MM/DD/RR HH:MI AM'
with sample_data_tab (User#, Time_Start, Time_End, Type) as (
select 'User1', to_date('3/29/23 08:00 AM', 'MM/DD/RR HH:MI AM'), to_date('3/29/23 08:00 PM', 'MM/DD/RR HH:MI AM'), 'Open'   from dual union all
select 'User1', to_date('3/29/23 12:00 PM', 'MM/DD/RR HH:MI AM'), to_date('3/29/23 04:00 PM', 'MM/DD/RR HH:MI AM'), 'Closed' from dual union all
select 'User2', to_date('3/29/23 10:00 AM', 'MM/DD/RR HH:MI AM'), to_date('3/29/23 10:00 PM', 'MM/DD/RR HH:MI AM'), 'Open'   from dual union all
select 'User2', to_date('3/29/23 02:00 PM', 'MM/DD/RR HH:MI AM'), to_date('3/29/23 06:00 PM', 'MM/DD/RR HH:MI AM'), 'Closed' from dual
)
SELECT User#
, CASE WHEN Time_Start < LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End) 
            THEN Time_End 
       ELSE Time_Start
  END Time_Start
, CASE WHEN Time_End < LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End) 
        AND Time_Start < LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End)
            THEN LAG(Time_End, 1)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End)
       ELSE LEAD(Time_Start, 1, Time_End)OVER(PARTITION BY User# ORDER BY Time_Start, Time_End)
  END Time_End
--, TIME_START as old_TIME_START
--, TIME_END as old_TIME_END
from sample_data_tab t
ORDER BY User#, Time_Start, Time_End


USER#
TIME_START
TIME_END


User1
03/29/23 08:00 AM
03/29/23 12:00 PM

User1
03/29/23 04:00 PM
03/29/23 08:00 PM

User2
03/29/23 10:00 AM
03/29/23 02:00 PM

User2
03/29/23 06:00 PM
03/29/23 10:00 PM

USER#	TIME_START	TIME_END
User1	03/29/23 08:00 AM	03/29/23 12:00 PM
User1	03/29/23 04:00 PM	03/29/23 08:00 PM
User2	03/29/23 10:00 AM	03/29/23 02:00 PM
User2	03/29/23 06:00 PM	03/29/23 10:00 PM