Hi,
I am trying to calculate Total, Overlap & Net Hours for the following data.
with data as (
select 1111 as person_id, '01-AUG-11' as event_date, to_date('01-AUG-11 18:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 06:00', 'DD-MON-YY Hh24:MI') as end_time, 'AAA' project_id,'Y' as mandatory_work from dual union all
select 1111 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 03:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 04:00', 'DD-MON-YY Hh24:MI') as end_time, 'BBB' project_id ,'Y' as mandatory_work from dual union all
select 1111 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 05:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 07:15', 'DD-MON-YY Hh24:MI') as end_time, 'CCC' project_id,'N' as mandatory_work from dual union all
select 1111 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 06:16', 'DD-MON-YY Hh24:MI') as start_time, to_date('02-AUG-11 07:00', 'DD-MON-YY Hh24:MI') as end_time, 'CCC' project_id,'N' as mandatory_work from dual union all
select 1111 as person_id, '03-AUG-11' as event_date, to_date('03-AUG-11 11:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 15:00', 'DD-MON-YY Hh24:MI') as end_time, 'AAA' project_id,'Y' as mandatory_work from dual union all
select 2222 as person_id, '02-AUG-11' as event_date, to_date('02-AUG-11 18:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 08:00', 'DD-MON-YY Hh24:MI') as end_time, 'AAA' project_id,'Y' as mandatory_work from dual union all
select 2222 as person_id, '03-AUG-11' as event_date, to_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') as end_time, 'BBB' project_id,'Y' as mandatory_work from dual union all
select 2222 as person_id, '03-AUG-11' as event_date, to_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') as start_time, to_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') as end_time, 'CCC' project_id,'Y' as mandatory_work from dual
)
--
-- end of test data
--
select
person_id, start_time, end_time, project_id,mandatory_work,
(end_time - start_time) * 24 hours,
(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) overlap_hrs,
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) Net_hrs
from data
order by
person_id, mandatory_work desc, start_time, end_time
;
However, there is a twist in requirement for a specific calendar date.
First overlap is calculated for person, date & Mandatory_work= "Y".
Take min of start_date & max of end_date with Mandatory _work=’Y’ for given calendar date.
a. If Mandatory _work=’N’ is between min of start_date & max of end_date with Mandatory_work=’Y’ then Net Hrs. is zero Otherwise delta between those two dates.
b. If mandatory_work=’N’ is NOT between min of shift_start_date & max of shift_end_date with mandatory_work=’Y’ then Net Hrs. is NOT zero AND total Hrs is Net Hours & overlap Hrs. is zero.