I'm trying to calculate the number of days,hours, minutes, seconds between two timestamps (working time) which excludes holidays, weekends and nonworking hours.
I figured out how to exclude weekends. Valid working hours are from 730am - 1630Pm. In my example I figured out how to exclude whole hours but I'm unsure how to deal with times >0 minutes.
For the holidays I was thinking about the MINUS function to exclude them but I couldn't get it to work.
Below is my attempt. Any help would be greatly appreciated. Thanks in advance to all who answer.
create table holidays(
holiday_date DATE,
holiday_name VARCHAR2(20),
constraint holidays_pk primary key (holiday_date),
constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
);
INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
WITH dts as (
select to_date('14-FEB-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Valentines Day 2022' from dual union all
select to_date('21-FEB-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Presidents Day 2022' from dual
)
SELECT * from dts;
with t (start_date
,end_date
)
as (select to_date('01-FEB-2022 13:00:00','dd-mon-yyyy hh24:mi:ss')
,to_date('28-FEB-2022 13:00:00','dd-mon-yyyy hh24:mi:ss')
from dual
)
, hrs (dt) as
(select start_date
from t
union all
select dt +1/24
from hrs
where hrs.dt < (select end_date-1/24 from t)
)
select count(*)
from hrs
where to_char(dt,'dy') not in ('sat','sun')
and to_number(to_char(dt,'hh24')) not between 17 and 23
and to_number(to_char(dt,'hh24')) not between 0 and 6