with t
as
(
--Объект Work_id 1-------------------------------------------------------------------------------------------------------------------------------------------------------------------
select 1 WORK_ID, 1 as team_id, 8 START_DATE, 14 as END_DATE, 1 sort_id, 3 as DURATION_IN_HOURS  from dual
UNION ALL
select  1 WORK_ID,2 as team_id, 07 START_DATE, 15 as END_DATE, 2 sort_id, 4 as DURATION_IN_HOURS  from dual 
UNION ALL
select 1 WORK_ID, 3 as team_id, 10 START_DATE, 17 as END_DATE, 2 sort_id, 3 as DURATION_IN_HOURS  from dual
UNION ALL
select 1 WORK_ID, 4 as team_id, 14 START_DATE, 22 as END_DATE, 2 sort_id, 4 as DURATION_IN_HOURS   from dual
UNION ALL --Объект Work_id 2-------------------------------------------------------------------------------------------------------------------------------------------------------
select 2 WORK_ID, 1 as team_id, 12 START_DATE, 15 as END_DATE, 1 sort_id, 3 as DURATION_IN_HOURS   from dual
UNION ALL
select 2 WORK_ID, 2 as team_id, 10 START_DATE, 16 as END_DATE, 2 sort_id, 3 as DURATION_IN_HOURS   from dual 
UNION ALL
select 2 WORK_ID, 3 as team_id, 13 START_DATE, 18 as END_DATE, 2 sort_id, 2 as DURATION_IN_HOURS   from dual 
UNION ALL
select 2 WORK_ID, 4 as team_id, 18 START_DATE, 23 as END_DATE, 2 sort_id, 5 as DURATION_IN_HOURS   from dual 
UNION ALL
select  2 WORK_ID,5 as team_id, 19 START_DATE, 22 as END_DATE, 2 sort_id, 0 as DURATION_IN_HOURS   from dual 
)
,intervals as (
select t2.team_id t2w_id
,greatest(t1.start_date,t2.start_date) b
,least(t1.end_date,t2.end_date) e
,t1.* from t t1,t t2
where t2.work_id(+)=t1.work_id and t2.team_id(+)<t1.team_id
and t1.start_date<t2.end_date(+) and t2.start_date(+)<t1.end_date --пересекаются
)
select work_id,team_id
      ,max(start_date) start_date,max(end_date) end_date
      ,sum(x_e-x_b) delta
      ,(max(end_date)-max(start_date))-nvl(sum(x_e-x_b),0) work_time
from
(select work_id,team_id,grp_id,min(b) as x_b, max(e) as x_e
       ,max(start_date) start_date,max(end_date) end_date
  from (
         select i2.*, sum(sog) over(partition by work_id,team_id order by b,e) as grp_id
           from (
                  select i.*, 
                         case 
                           when b <= max(e) over(partition by work_id,team_id order by b, e 
                                                  rows between unbounded preceding
                                                           and 1 preceding) 
                             then 0 
                           else 1 
                         end as sog -- sog = start_of_group :)
                    from intervals i
                ) i2
       ) 
 group by work_id,team_id,grp_id
)group by work_id,team_id
order by work_id,team_id
--,t2w_id
/
SQL> /

   WORK_ID    TEAM_ID START_DATE   END_DATE      DELTA  WORK_TIME
---------- ---------- ---------- ---------- ---------- ----------
         1          1          8         14                     6
         1          2          7         15          6          2
         1          3         10         17          5          2
         1          4         14         22          3          5
         2          1         12         15                     3
         2          2         10         16          3          3
         2          3         13         18          3          2
         2          4         18         23                     5
         2          5         19         22          3          0

9 rows selected.