Splitting times given 2 intevals | Oracle SQL

I'm working for an organization where users sometimes work on a "split" schedule, and the data is structured like this as an example:

Problem is I'll need to know their working hours without their "closed" hours -- how do I make it so that the end result would look something like this:

I've tried using PARTITION BY but for the love of me can't get the result I wanted... probably am doing it wrong

Here is query that could be used.

It relies on quite heavy assumptions:

• all shifts contained within same day,
• every open shift contains not more than one closed shift,
• closed shift contained fully inside of open counterpart,
• no closed shifts exists without corresponding open one.

With
    pairs as (
        select
            jc.user,
            jo.timestart o_start,
            jc.timestart c_start,
            jc.timeend c_end,
            jo.timeend o_end
        from
            journal jo
            join journal jc on (
                j1.user = j2.user
                and trunc (j1.timestart) = trunc (j2.timestart)
                and jc.type = 'Closed'
            )
        Where
            jo.type = 'Open'
    ),
    open_only as (
        --Only open ones
        select
            j1.user,
            j1.timestart,
            jc.timeend
        from
            journal j1
        where
            type = 'Open' not exists (
                select
                    1
                from
                    journal j2
                where
                    j1.user = j2.user
                    and trunc (j1.timestart) = trunc (j2.timestart)
                    and j2.type = 'Closed'
            )
    )
select
    user,
    timestart,
    timeend
from
    open_only
UNION ALL -- First part of shift
select
    user,
    o_start,
    c_start
from
    pairs
UNION ALL -- First part of shift
select
    user,
    c_end,
    o_end
from
    pairs

This query unions three parts:

1. Only open shifts
2. First part of shift (from beginning of open part, to beginning of closed part)
3. Second part of shift (from ending of closed part to ending of open part)

You can UNPIVOT the times and then use analytic functions to count whether you have more start than end times for both open and closed and filter to only have those ranges that are part of an open range and not part of a closed range.

(Note: this will handle: multiple closed ranges within an open range; overlapping ranges of the same type; ranges that span days; and closed ranges without a containing open range.)

SELECT user_name,
       time_start,
       time_end
FROM   (
  SELECT user_name,
         dt AS time_start,
         LEAD(dt) OVER (PARTITION BY user_name ORDER BY dt) AS time_end,
         SUM(CASE type WHEN 'Open' THEN is_open END )
           OVER (PARTITION BY user_name ORDER BY dt) AS is_open,
         SUM(CASE type WHEN 'Closed' THEN is_open ELSE 0 END )
           OVER (PARTITION BY user_name ORDER BY dt) AS is_closed
  FROM   table_name
  UNPIVOT ( dt FOR is_open IN ( time_start AS 1, time_end AS -1 ) )
)
WHERE  is_open > 0
AND    is_closed = 0

Which, for the sample data:

CREATE TABLE table_name (user_name,	Time_Start, Time_End, Type) AS
SELECT 'User1', DATE '2023-03-29' + INTERVAL '08:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '20:00:00' HOUR TO SECOND,	'Open'   FROM DUAL UNION ALL
SELECT 'User1', DATE '2023-03-29' + INTERVAL '12:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '16:00:00' HOUR TO SECOND,	'Closed' FROM DUAL UNION ALL
SELECT 'User2', DATE '2023-03-29' + INTERVAL '10:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '22:00:00' HOUR TO SECOND,	'Open'   FROM DUAL UNION ALL
SELECT 'User2', DATE '2023-03-29' + INTERVAL '14:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '18:00:00' HOUR TO SECOND,	'Closed' FROM DUAL UNION ALL
SELECT 'User3', DATE '2023-03-29' + INTERVAL '10:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '22:00:00' HOUR TO SECOND,	'Open'   FROM DUAL UNION ALL
SELECT 'User3', DATE '2023-03-29' + INTERVAL '11:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '12:00:00' HOUR TO SECOND,	'Closed' FROM DUAL UNION ALL
SELECT 'User3', DATE '2023-03-29' + INTERVAL '13:00:00' HOUR TO SECOND, DATE '2023-03-29' + INTERVAL '14:00:00' HOUR TO SECOND,	'Closed' FROM DUAL;

Outputs:

fiddle

You can use the solution below to solve your problem. it uses the analytic functions LAG and LEAD to implement the required logic.
I assume you don't have more than one closed ranges within an open range.

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
from sample_data_tab t
ORDER BY User#, Time_Start, Time_End

demo

Maybe you'd like to have shifts and breaks of a user in a single row. It could be done using a combination of analyitic functions LEAD() & LAG() with Case expressions. The code is adjusted for sample data below and it's limited to max of two breaks in a working day (should be enough, though)

WITH    --  Sample Data
	tbl (USER_NAME, STARTS, ENDS, STATUS) AS
		( Select 'User1', To_Date('2023-03-29 08:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 20:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Open'   From Dual Union All
			Select 'User1', To_Date('2023-03-29 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 16:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' From Dual Union All
			Select 'User2', To_Date('2023-03-29 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 22:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Open'   From Dual Union All
			Select 'User2', To_Date('2023-03-29 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 18:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' From Dual Union All
			Select 'User3', To_Date('2023-03-29 10:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 22:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Open'   From Dual Union All
			Select 'User3', To_Date('2023-03-29 11:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 12:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' From Dual Union All
			Select 'User3', To_Date('2023-03-29 13:00:00', 'yyyy-mm-dd hh24:mi:ss'), To_Date('2023-03-29 14:00:00', 'yyyy-mm-dd hh24:mi:ss'), 'Closed' FROM Dual
		),
  breaks AS   -- cte breaks
    ( Select  ROW_NUMBER() OVER(Partition By USER_NAME, TRUNC(STARTS) Order By USER_NAME, STARTS) "BREAK_NO",
              COUNT(*) OVER(Partition By USER_NAME, TRUNC(STARTS)) "TOTAL_BREAKS",
              USER_NAME, STARTS, ENDS
      From tbl Where STATUS = 'Closed'
      Order By USER_NAME, STARTS
    )

--  M a i n   S Q L
SELECT  USER_NAME, 
        SHIFT_1, SHIFT_1_ENDS, BREAK_1, BREAK_1_UNTIL,
        SHIFT_2, SHIFT_2_ENDS, BREAK_2, BREAK_2_UNTIL,
        SHIFT_3, SHIFT_3_ENDS
FROM    (   Select      b.BREAK_NO, w.USER_NAME "USER_NAME", TRUNC(w.STARTS) "WORK_DATE",
                        To_Char(w.STARTS, 'hh24:mi') "SHIFT_1", CASE WHEN b.BREAK_NO = 1 THEN To_Char(b.STARTS, 'hh24:mi') END "SHIFT_1_ENDS",
                        To_Char(b.STARTS, 'hh24:mi') "BREAK_1", CASE WHEN b.BREAK_NO = 1 THEN To_Char(b.ENDS, 'hh24:mi') END "BREAK_1_UNTIL",
                        --
                        To_Char(b.ENDS, 'hh24:mi') "SHIFT_2",
                        CASE  WHEN b.TOTAL_BREAKS = 1 And b.BREAK_NO = 1 
                              THEN To_Char(w.ENDS, 'hh24:mi') 
                        ELSE  CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.STARTS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS) END 
                        END "SHIFT_2_ENDS",
                        --
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.STARTS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS)  END "BREAK_2",
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.ENDS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS)  END "BREAK_2_UNTIL",
                        --
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  LEAD(To_Char(b.ENDS, 'hh24:mi')) OVER(Partition By b.USER_NAME, TRUNC(b.STARTS) Order By b.USER_NAME, b.STARTS)  END "SHIFT_3",
                        CASE WHEN b.TOTAL_BREAKS = 2 And b.BREAK_NO = 1 THEN  To_Char(w.ENDS, 'hh24:mi')  END "SHIFT_3_ENDS"
            From        tbl w
            Inner Join  breaks b ON(b.USER_NAME = w.USER_NAME And TRUNC(b.STARTS) = TRUNC(w.STARTS) And w.STATUS = 'Open')
        )
Where   BREAK_NO = 1

--
--  R e s u l t :
USER_NAME SHIFT_1 SHIFT_1_ENDS BREAK_1 BREAK_1_UNTIL SHIFT_2 SHIFT_2_ENDS BREAK_2 BREAK_2_UNTIL SHIFT_3 SHIFT_3_ENDS
--------- ------- ------------ ------- ------------- ------- ------------ ------- ------------- ------- ------------
User1     08:00   12:00        12:00   16:00         16:00   20:00                                                   
User2     10:00   14:00        14:00   18:00         18:00   22:00                                                   
User3     10:00   11:00        11:00   12:00         12:00   13:00        13:00   14:00         14:00   22:00      

If you want just working hours - all you should do is select just them in the Select list of a main SQL:

SELECT  USER_NAME, 
        SHIFT_1, SHIFT_1_ENDS, --BREAK_1, BREAK_1_UNTIL,   breaks excluded
        SHIFT_2, SHIFT_2_ENDS, --BREAK_2, BREAK_2_UNTIL,
        SHIFT_3, SHIFT_3_ENDS
FROM ...  ...  ...  ...

--
--  R e s u l t :
USER_NAME SHIFT_1 SHIFT_1_ENDS SHIFT_2 SHIFT_2_ENDS SHIFT_3 SHIFT_3_ENDS
--------- ------- ------------ ------- ------------ ------- ------------
User1     08:00   12:00        16:00   20:00                             
User2     10:00   14:00        18:00   22:00                             
User3     10:00   11:00        12:00   13:00        14:00   22:00