Forum Stats

  • 3,759,245 Users
  • 2,251,515 Discussions
  • 7,870,550 Comments

Discussions

Accurate Overlap Hours Calculations is not correct- Please help

User_YL3T0
User_YL3T0 Member Posts: 8 Green Ribbon
edited Sep 9, 2021 8:31AM in SQL & PL/SQL

I have a following code but doesn't calculate Net, Overlap & Total Hours Accurately in a shift starting from 14:00:00 to 21:59:59

Can someone please help? I am using Oracle 11g R2

WITH data 

   AS (SELECT 1111                      AS person_id, 

        '03-06-21'                   AS event_date, 

        To_date('03-jun-21 14:00:00', 'DD-MON-YY Hh24:MI:SS') AS start_time, 

        To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS') AS end_time, 

        'AAA'                      project_id, 

        'Y'                       AS 

        mandatory_work 

     FROM  dual 

     UNION ALL 

     SELECT 1111                      AS person_id, 

        '03-06-21'                   AS event_date, 

        To_date('3-jun-21 15:35:08', 'DD-MON-YY Hh24:MI:SS') AS start_time, 

        To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS') AS end_time, 

        'BBB'                      project_id, 

        'Y'                       AS 

        mandatory_work 

     FROM  dual 

     UNION ALL 

     SELECT 1111                      AS person_id, 

        '03-06-21'                   AS event_date, 

        To_date('03-jun-21 14:37:46', 'DD-MON-YY Hh24:MI:SS') AS start_time, 

        To_date('03-jun-21 14:42:46', 'DD-MON-YY Hh24:MI:SS') AS end_time, 

        'CCC'                      project_id, 

        'N'                       AS 

        mandatory_work 

     FROM  dual 

     UNION ALL 

     SELECT 1111                      AS person_id, 

        '03-06-21'                   AS event_date, 

        To_date('03-jun-21 18:08:46', 'DD-MON-YY Hh24:MI:SS') AS start_time, 

        To_date('03-jun-21 18:13:46', 'DD-MON-YY Hh24:MI:SS') AS end_time, 

        'CCC'                      project_id, 

        'N'                       AS 

        mandatory_work 

     FROM  dual 

 ) 

-- 

-- end of test data 

-- 

SELECT person_id, 

    start_time, 

    end_time, 

    project_id, 

    mandatory_work, 

    round(( end_time - start_time ) * 24 ,2)             hours, 

    round(( end_time - start_time ) * 24 - Greatest(( end_time - Greatest( 

                          start_time, Nvl( 

                               Lag(end_time) 

    over ( 

     PARTITION BY person_id, mandatory_work 

     ORDER BY mandatory_work DESC, start_time 

    ASC, 

    end_time DESC), start_time 

                     )) ) * 24, 0) ,2)     overlap_hrs, 

    round(Greatest(( end_time - Greatest(start_time, Nvl(Lag(end_time) 

               over ( 

                PARTITION BY person_id, mandatory_work 

                ORDER BY mandatory_work DESC, 

               start_time 

               ASC, 

               end_time DESC), start_time 

                         )) ) * 24, 0),2) Net_hrs 

FROM  data 

ORDER BY person_id, 

     mandatory_work DESC, 

     start_time, 

     end_time; 
Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    Thanks for posting the data and what you've already tried.

    You haven't shown what results you're expecting from your test data.

    It looks as though you want to break it down to mandatory and non-mandatory work, but what about when the non-mandatory and mandatory work also overlap (rather than just the projects within mandatory/non-mandatory overlapping)?

    The solution will depend on various factors, including your database version, so it's always good to say what version of the database you are using.

    Looking for overlapping projects within the time frames is going to be the tricky bit, hence my question around the need to clarify if it matters that there are overlaps between the mandatory flags too?

    Solutions would no doubt range from using match_recognize, to using various analytical functions etc.

    So, as an example, you could break it down to each minute of the working shift and determine what was being worked on at any one minute (if really necessary you could go down to the second), and that could give you details such as...

    SQL> WITH data(person_id, event_date, start_time, end_time, project_id, mandatory_work) AS (
      2    SELECT 1111, '03-06-21', To_date('03-jun-21 14:00:00', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'AAA','Y' FROM dual UNION ALL
      3    SELECT 1111, '03-06-21', To_date('03-jun-21 15:35:08', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'BBB','Y' FROM dual UNION ALL
      4    SELECT 1111, '03-06-21', To_date('03-jun-21 14:37:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 14:42:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual UNION ALL
      5    SELECT 1111, '03-06-21', To_date('03-jun-21 18:08:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 18:13:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual
      6    )
      7  --
      8  -- end of test data
      9  --
     10   ,cal_min as (
     11      -- generate a calendar of minute by minute for the shift for the person
     12      -- and map each record to the relevant minutes in the calendar
     13      select d.person_id
     14            ,d.event_date
     15            ,d.start_time
     16            ,d.end_time
     17            ,d.project_id
     18            ,d.mandatory_work
     19            ,t.tm
     20      from   data d
     21             join (select person_id, event_date, start_time+((level-1)/24/60) as tm
     22                   from  (select person_id, event_date
     23                                ,min(start_time) as start_time
     24                                ,max(end_time) as end_time
     25                          from   data
     26                          group by person_id, event_date
     27                         )
     28                   connect by person_id = prior person_id
     29                          and event_date = prior event_date
     30                          and level <= ((end_time-start_time)*(24*60))+1
     31                          and prior sys_guid() is not null
     32                  ) t on (t.person_id = d.person_id and t.event_date = d.event_date and t.tm between d.start_time and d.end_time)
     33      )
     34     ,overlap as (
     35      -- per person, event_date and mandatory/non-mandatory, determine the date range overall
     36      -- and whether any minute in the calendar has more than one project identified against it (overlap)
     37      select person_id
     38            ,event_date
     39            ,mandatory_work
     40            ,tm
     41            ,min(start_time) as start_time
     42            ,max(end_time) as end_time
     43            ,case when listagg('X',',') within group (order by project_id) like '%,%' then 'Y' else 'N' end as overlap
     44      from   cal_min c
     45      group by person_id, event_date, mandatory_work, tm
     46     )
     47  -- now count the number of minutes to determine hours/mins worked for each person/event/mandatory grouping
     48  select person_id
     49        ,event_date
     50        ,mandatory_work
     51        ,start_time
     52        ,end_time
     53        ,overlap
     54        ,floor(count(*)/60) as hrs_worked
     55        ,mod(count(*),60) as mins_worked
     56  from   overlap
     57  group by person_id, event_date, mandatory_work, start_time, end_time, overlap
     58  /
    
    
     PERSON_ID EVENT_DA M START_TIME           END_TIME             O HRS_WORKED MINS_WORKED
    ---------- -------- - -------------------- -------------------- - ---------- -----------
          1111 03-06-21 N 03-JUN-2021 14:37:46 03-JUN-2021 14:42:46 N          0           5
          1111 03-06-21 N 03-JUN-2021 18:08:46 03-JUN-2021 18:13:46 N          0           5
          1111 03-06-21 Y 03-JUN-2021 14:00:00 03-JUN-2021 21:59:59 Y          6          24
          1111 03-06-21 Y 03-JUN-2021 14:00:00 03-JUN-2021 21:59:59 N          1          36
    


    Now, that's not a complete solution and doesn't necessarily give you all the details you were looking for (nor is it necessarily accurate as the mandatory projects cover a net time of 7h58m due to differences in seconds), but is just an idea of the complexity of what you're asking (or what I think you're asking) which would then need further work to sum up for the overall NET time etc.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    Just to add... for "accuracy" you need to be clear on how the seconds should be handled...

    In one of your examples you have the shift/time from 14:00:00 to 21:59:59, so if we take that as "inclusive seconds" we can say that is a full 8 hours (the 00:00 minute/second being worked as was the 59:59 minute/second)

    Yet in your other example you have shift/time from 18:08:46 to 18:13:46, which if we take the same "inclusive" principle would be 5 minutes and 1 second (so if we're looking at a level of minutes, should that be 5, or 6?)

    This is a good example of why it's necessary to state exactly what output you're expecting from your test data.

  • User_YL3T0
    User_YL3T0 Member Posts: 8 Green Ribbon
    edited Sep 9, 2021 10:34AM

    Thank you @BluShadow , Let me execute your code & suggestion. Also I want to add some additional information that helps solve the problem.

    1. I am this time not concerned with mandatory or optional for now.. but all I want to calculate accurately Net Hrs, Overlap Hrs & total Hrs. for partitioning for person id & event date. Apologies, I forgot to mention the same in original query.
    2. New Query is listed as follows ( Technically there is no change except I changed Partition to be on person_id & event_date:
    WITH data(person_id, event_date, start_time, end_time, project_id, mandatory_work) AS (
    SELECT 1111, '03-06-21', To_date('03-jun-21 14:00:00', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'AAA','Y' FROM dual UNION ALL
    SELECT 1111, '03-06-21', To_date('03-jun-21 15:35:08', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'BBB','Y' FROM dual UNION ALL
    SELECT 1111, '03-06-21', To_date('03-jun-21 14:37:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 14:42:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual UNION ALL
    SELECT 1111, '03-06-21', To_date('03-jun-21 18:08:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 18:13:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual
    ) 
    -- 
    -- end of test data 
    -- 
    SELECT person_id, event_date,start_time,end_time,project_id, mandatory_work, 
        round(( end_time - start_time ) * 24 ,2)             hours, 
    
    
        round(( end_time - start_time ) * 24 - 
        Greatest(( end_time - Greatest( start_time, Nvl( Lag(end_time) 
       over ( PARTITION BY person_id , event_date ORDER BY start_time,end_time DESC), 
       start_time)) ) * 24, 0) ,2)overlap_hrs, 
    
    
        round(Greatest(( end_time - Greatest(start_time, Nvl(Lag(end_time) 
        over (PARTITION BY person_id , event_date ORDER BY start_time,end_time DESC),
        start_time )) ) * 24, 0),2) Net_hrs 
    FROM  data 
    ORDER BY person_id,event_date,mandatory_work DESC,start_time, end_time; 
    

    Following is the output:

    if you look at the above all the date ranges overlaps with first row. so it should give me total 8 hours instead it provide 8 +6.41 = 13.41 hours.

    One more point I want to add that, I need to retain all columns & rows as the original data source.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    Ok, again I'm just breaking this down into individual steps, so it's not necessarily optimum, but if we can ignore the mandatory/optional...

    SQL> WITH data(person_id, event_date, start_time, end_time, project_id, mandatory_work) AS (
      2    SELECT 1111, '03-06-21', To_date('03-jun-21 14:00:00', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'AAA','Y' FROM dual UNION ALL
      3    SELECT 1111, '03-06-21', To_date('03-jun-21 15:35:08', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'BBB','Y' FROM dual UNION ALL
      4    SELECT 1111, '03-06-21', To_date('03-jun-21 14:37:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 14:42:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual UNION ALL
      5    SELECT 1111, '03-06-21', To_date('03-jun-21 18:08:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 18:13:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual
      6    )
      7  --
      8  -- end of test data
      9  --
     10   ,cal_min as (
     11      -- generate a calendar of minute by minute for the shift for the person
     12      -- and map each record to the relevant minutes in the calendar
     13      select d.person_id
     14            ,d.event_date
     15            ,d.start_time
     16            ,d.end_time
     17            ,d.project_id
     18            ,d.mandatory_work
     19            ,t.tm
     20      from   data d
     21             join (select person_id, event_date, start_time+((level-1)/24/60) as tm
     22                   from  (select person_id, event_date
     23                                ,min(start_time) as start_time
     24                                ,max(end_time) as end_time
     25                          from   data
     26                          group by person_id, event_date
     27                         )
     28                   connect by person_id = prior person_id
     29                          and event_date = prior event_date
     30                          and level <= ((end_time-start_time)*(24*60))+1
     31                          and prior sys_guid() is not null
     32                  ) t on (t.person_id = d.person_id and t.event_date = d.event_date and t.tm between d.start_time and d.end_time)
     33      )
     34     ,overlap as (
     35      -- per person, event_date determine the date range overall
     36      -- and whether any minute in the calendar has more than one project identified against it (overlap)
     37      select person_id
     38            ,event_date
     39            ,tm
     40            ,min(start_time) as start_time
     41            ,max(end_time) as end_time
     42            ,case when listagg('X',',') within group (order by project_id) like '%,%' then 'Y' else 'N' end as overlap
     43      from   cal_min c
     44      group by person_id, event_date, tm
     45     )
     46    ,mins_breakdown as (
     47     -- now count the number of minutes to determine hours/mins worked for each person/event/mandatory grouping
     48      select person_id
     49            ,event_date
     50            --,mandatory_work
     51            ,start_time
     52            ,end_time
     53            ,overlap
     54            ,count(*)/60 as hrs
     55      from   overlap
     56      group by person_id, event_date, start_time, end_time, overlap
     57     )
     58  select person_id
     59        ,event_date
     60        ,start_time
     61        ,end_time
     62        ,overlap
     63        ,round(hrs,2) as hrs
     64        ,sum(hrs) over (partition by person_id, event_date) as net_hours
     65  from   mins_breakdown
     66  /
    
    
     PERSON_ID EVENT_DA START_TIME           END_TIME             O        HRS  NET_HOURS
    ---------- -------- -------------------- -------------------- - ---------- ----------
          1111 03-06-21 03-JUN-2021 14:00:00 03-JUN-2021 21:59:59 N       1.52          8
          1111 03-06-21 03-JUN-2021 14:00:00 03-JUN-2021 21:59:59 Y       6.48          8
    

    That gives us the number of hours of overlapping projects (including the optional) and the number of hours overall.

    or, if you want to exclude the optional projects from the overlap consideration, that can just be done via the listagg function with a conditional case statement...

    SQL> WITH data(person_id, event_date, start_time, end_time, project_id, mandatory_work) AS (
      2    SELECT 1111, '03-06-21', To_date('03-jun-21 14:00:00', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'AAA','Y' FROM dual UNION ALL
      3    SELECT 1111, '03-06-21', To_date('03-jun-21 15:35:08', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 21:59:59', 'DD-MON-YY Hh24:MI:SS'),'BBB','Y' FROM dual UNION ALL
      4    SELECT 1111, '03-06-21', To_date('03-jun-21 14:37:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 14:42:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual UNION ALL
      5    SELECT 1111, '03-06-21', To_date('03-jun-21 18:08:46', 'DD-MON-YY Hh24:MI:SS'),To_date('03-jun-21 18:13:46', 'DD-MON-YY Hh24:MI:SS'),'CCC','N' FROM dual
      6    )
      7  --
      8  -- end of test data
      9  --
     10   ,cal_min as (
     11      -- generate a calendar of minute by minute for the shift for the person
     12      -- and map each record to the relevant minutes in the calendar
     13      select d.person_id
     14            ,d.event_date
     15            ,d.start_time
     16            ,d.end_time
     17            ,d.project_id
     18            ,d.mandatory_work
     19            ,t.tm
     20      from   data d
     21             join (select person_id, event_date, start_time+((level-1)/24/60) as tm
     22                   from  (select person_id, event_date
     23                                ,min(start_time) as start_time
     24                                ,max(end_time) as end_time
     25                          from   data
     26                          group by person_id, event_date
     27                         )
     28                   connect by person_id = prior person_id
     29                          and event_date = prior event_date
     30                          and level <= ((end_time-start_time)*(24*60))+1
     31                          and prior sys_guid() is not null
     32                  ) t on (t.person_id = d.person_id and t.event_date = d.event_date and t.tm between trunc(d.start_time,'MI') and d.end_time)
     33      )
     34     ,overlap as (
     35      -- per person, event_date determine the date range overall
     36      -- and whether any minute in the calendar has more than one project identified against it (overlap)
     37      select person_id
     38            ,event_date
     39            ,tm
     40            ,min(start_time) as start_time
     41            ,max(end_time) as end_time
     42            ,case when listagg(case when mandatory_work = 'Y' then 'X' else null end,',') within group (order by project_id) like '%,%' then 'Y' else 'N' end as overlap
     43      from   cal_min c
     44      group by person_id, event_date, tm
     45     )
     46    ,mins_breakdown as (
     47     -- now count the number of minutes to determine hours/mins worked for each person/event/mandatory grouping
     48      select person_id
     49            ,event_date
     50            --,mandatory_work
     51            ,start_time
     52            ,end_time
     53            ,overlap
     54            ,count(*)/60 as hrs
     55      from   overlap
     56      group by person_id, event_date, start_time, end_time, overlap
     57     )
     58  select person_id
     59        ,event_date
     60        ,start_time
     61        ,end_time
     62        ,overlap
     63        ,round(hrs,2) as hrs
     64        ,sum(hrs) over (partition by person_id, event_date) as net_hours
     65  from   mins_breakdown
     66  /
    
    
     PERSON_ID EVENT_DA START_TIME           END_TIME             O        HRS  NET_HOURS
    ---------- -------- -------------------- -------------------- - ---------- ----------
          1111 03-06-21 03-JUN-2021 14:00:00 03-JUN-2021 21:59:59 N       1.58          8
          1111 03-06-21 03-JUN-2021 14:00:00 03-JUN-2021 21:59:59 Y       6.42          8
    

    Again, I'm still doing this to the minute rather than the seconds, hence my 6.42 compared to your 6.41. It could be "adjusted" in some way, but as I pointed out we either have to be "inclusive" or "exclusive" of the final seconds in the end time and your CCC project entries just go that 1 second over if we're inclusive.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    with data(person_id,event_date,start_time,end_time,project_id,mandatory_work)
       as (
           select 1111,'03-06-21',to_date('03-jun-21 14:00:00','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 21:59:59','dd-mon-yy hh24:mi:ss'),'AAA','Y' from dual union all
           select 1111,'03-06-21',to_date('03-jun-21 15:35:08','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 21:59:59','dd-mon-yy hh24:mi:ss'),'BBB','Y' from dual union all
           select 1111,'03-06-21',to_date('03-jun-21 14:37:46','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 14:42:46','dd-mon-yy hh24:mi:ss'),'CCC','N' from dual union all
           select 1111,'03-06-21',to_date('03-jun-21 18:08:46','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 18:13:46','dd-mon-yy hh24:mi:ss'),'CCC','N' from dual
          ),
    t1 as (
           select  d.*,
                   lag(end_time,1,end_time - 1) over(partition by person_id,event_date order by start_time,end_time desc) prev_end_time
             from  data d
          ),
    t2 as (
           select  t1.*,
                   round((end_time - start_time) * 24,2) hours,
                   round((greatest(end_time,prev_end_time) - greatest(start_time,prev_end_time)) * 24,2) net_hours
             from  t1
          )
    select  person_id,
            event_date,
            start_time,
            end_time,
            project_id,
            mandatory_work,
            hours,
            hours - net_hours overlap_hours,
            net_hours
      from  t2
      order by person_id,
               event_date,
               start_time,
               end_time desc
    /
    
     PERSON_ID EVENT_DATE START_TIME          END_TIME            PROJECT_ID MANDATORY_WORK      HOURS OVERLAP_HOURS  NET_HOURS
    ---------- ---------- ------------------- ------------------- ---------- -------------- ---------- ------------- ----------
          1111 03-06-21   06/03/2021 14:00:00 06/03/2021 21:59:59 AAA        Y                       8             0          8
          1111 03-06-21   06/03/2021 14:37:46 06/03/2021 14:42:46 CCC        N                     .08           .08          0
          1111 03-06-21   06/03/2021 15:35:08 06/03/2021 21:59:59 BBB        Y                    6.41             0       6.41
          1111 03-06-21   06/03/2021 18:08:46 06/03/2021 18:13:46 CCC        N                     .08           .08          0
    
    SQL>
    

    SY.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    edited Sep 9, 2021 11:41AM

    Solomon... why does your project BBB have overlap_hours of 0? Surely it should be 6.41 as it overlaps with project AAA? At least that's my understanding of the OP's requirement... (I certainly could be wrong! 😁 )

    Edit: Ah, I see the OP's spreadsheet has it as 0.00 as well. I guess I'm wrong then.

    Another example where we need detailed requirements and logic explaining I guess.

    Edit again: I guess it was the OP saying "if you look at the above all the date ranges overlaps with first row" that confused me, as it implies that everything is overlapping the AAA which covers the full shift, so I was expecting BBB to be considered as an overlap too.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,743 Red Diamond
    edited Sep 9, 2021 1:08PM

    Agreed OP's requirements are strange. Same as you, I'd use:

    with data(person_id,event_date,start_time,end_time,project_id,mandatory_work)
       as (
           select 1111,'03-06-21',to_date('03-jun-21 14:00:00','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 21:59:59','dd-mon-yy hh24:mi:ss'),'AAA','Y' from dual union all
           select 1111,'03-06-21',to_date('03-jun-21 15:35:08','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 21:59:59','dd-mon-yy hh24:mi:ss'),'BBB','Y' from dual union all
           select 1111,'03-06-21',to_date('03-jun-21 14:37:46','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 14:42:46','dd-mon-yy hh24:mi:ss'),'CCC','N' from dual union all
           select 1111,'03-06-21',to_date('03-jun-21 18:08:46','dd-mon-yy hh24:mi:ss'),to_date('03-jun-21 18:13:46','dd-mon-yy hh24:mi:ss'),'CCC','N' from dual
          ),
    t1 as (
           select  d.*,
                   nvl(
                       max(end_time) over(
                                          partition by person_id,
                                                       event_date
                                          order by start_time,
                                                   end_time desc
                                          rows between unbounded preceding
                                                   and 1 preceding
                                         ),
                       end_time - 1
                      ) prev_max_end_time
             from  data d
          ),
    t2 as (
           select  t1.*,
                   round((end_time - start_time) * 24,2) hours,
                   round((greatest(end_time,prev_max_end_time) - greatest(start_time,prev_max_end_time)) * 24,2) net_hours
             from  t1
          )
    select  person_id,
            event_date,
            start_time,
            end_time,
            project_id,
            mandatory_work,
            hours,
            hours - net_hours overlap_hours,
            net_hours
      from  t2
      order by person_id,
               event_date,
               start_time,
               end_time desc
    /
    
     PERSON_ID EVENT_DATE START_TIME          END_TIME            PROJECT_ID MANDATORY_WORK      HOURS OVERLAP_HOURS  NET_HOURS
    ---------- ---------- ------------------- ------------------- ---------- -------------- ---------- ------------- ----------
          1111 03-06-21   06/03/2021 14:00:00 06/03/2021 21:59:59 AAA        Y                       8             0          8
          1111 03-06-21   06/03/2021 14:37:46 06/03/2021 14:42:46 CCC        N                     .08           .08          0
          1111 03-06-21   06/03/2021 15:35:08 06/03/2021 21:59:59 BBB        Y                    6.41          6.41          0
          1111 03-06-21   06/03/2021 18:08:46 06/03/2021 18:13:46 CCC        N                     .08           .08          0
    
    SQL>
    

    SY.

    BluShadow