We use a bi-monthly payroll where employees get paid on the 15th and last days of the month.
If those days fall on a Saturday or Sunday then we get paid on the prior day(Friday) if that isn't defined as a holiday.
For example, take this week, April 15th is a Friday but its defined as a holiday so people should be paid on Thursday April 14. Dec 31 2022 is a Monday and a holiday, in addition Dec 29,30 are Saturday and Sunday so people should be paid on Friday Dec 28....
I managed to get a partial query working where I can exclude weekends and holidays but I can use some help figuring out what DATE people should be paid on. My output should only include pay days.
Thanks in advance to all who answer and for your patience, help and expertise.
Below is what I have so far with a sample output based on the 2 holidays I've included.
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('15-APR-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Passover 2022' from dual union all
select to_date('31-DEC-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'New Year Eve 2022' from dual
)
SELECT * from dts;
SELECT
COLUMN_VALUE
FROM
TABLE(generate_dates_pipelined(DATE '2022-01-01',
DATE '2022-12-31')) c
where
to_char(COLUMN_VALUE, 'DY') NOT IN ('SAT', 'SUN')
AND NOT EXISTS (
SELECT 1
FROM holidays h
WHERE c.COLUMN_VALUE = h.holiday_date
);
Output
Payday
14-JAN-2022 Friday
31-JAN-2022 Monday
15-FEB-2022 Tuesday
28-FEB-2022 Monday
15-MAR-2022 Tuesday
31-MAR-2022 Thursday
14-ARR-2022 Thursday
29-APR-2022 Friday
…
…