I'm trying to calculate the number of hours between two timestamps excluding holidays, weekends and working hours.
I figured out how to exclude weekends. Valid working hours are from 730am - 1630Pm. I'm unsure how to exclude half hours and holidays.
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'), 'Valentine's 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