I have some pieces of code that I'm trying to put together to generate 2 different queries, which counts the number of business days excluding weekends and holidays.
I want to use the generate_dates_pipelined to generate the dates so I don't have to store the VALUES in the database.
One query should generate counts that fall between the date ranges.
My input.
generate_dates_pipelined(DATE '2021-11-01', DATE '2021-11-30');
Expected output
Total_days business_days holidays
30 22 1
Second query is a list of days from the range.
I don't want to use NLS settings for SAT/SUN and prefer to use IW day week settings where sat=6 and sun=7 I believe.
CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
CREATE OR REPLACE FUNCTION generate_dates_pipelined(
p_from IN DATE,
p_to IN DATE
)
RETURN nt_date PIPELINED DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
LOOP
PIPE ROW (v_start);
EXIT WHEN v_start >= v_end;
v_start := v_start + INTERVAL '1' DAY;
END LOOP;
RETURN;
END generate_dates_pipelined;
/
create table holidays(
holiday_date DATE not null,
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('25-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Thanksgiving 2021' from dual union all
select to_date('29-NOV-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Hanukkah 2021' from dual
)
SELECT * from dts;
I know I need something like this in the SQL and a not in 6,7 test but I'm unsure how to put this all together.
-- exclude holidays
WHERE NOT EXISTS (
SELECT 1
FROM holidays h
WHERE c.COLUMN_VALUE = h.holiday_date
)