Oracle calculate pay DATE-CodePudding

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 if that isn't defined as a holiday.

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. Those are the ONLY days I want to see in the output

Thanks in advance to all who answer and for your patience, help and expertise.

Below is what I have so far.


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
           );

CodePudding user response：

You can use below to generate the date calendar that excludes Saturday and Sunday and then use that to filter passover and 31st Dec as needed.

with cte as (
select
decode(mod(level-1,2),
0,
add_months(trunc(sysdate,'YEAR') 14,
ceil(level/2)-1),
last_day(add_months(trunc(sysdate,'YEAR'),
ceil(level/2)-1))) day
from dual
connect by level < 25),
cte2 as (
select 
case
when instr(to_char(day,'DAY'),'SUNDAY')>0 then day-2
when instr(to_char(day,'DAY'),'SATURDAY')>0 then day-1
else day END as day_adjusted, to_char(day,'DAY') dow_orig
from cte)
select * from cte2;

Above query will yield output as, which can be further filtered as needed -

DAY_ADJUSTED          DOW_ORIG
--------------------- ---------------------
14-JAN-22             SATURDAY
31-JAN-22             MONDAY
15-FEB-22             TUESDAY
28-FEB-22             MONDAY
15-MAR-22             TUESDAY
31-MAR-22             THURSDAY
15-APR-22             FRIDAY
29-APR-22             SATURDAY
13-MAY-22             SUNDAY
31-MAY-22             TUESDAY
15-JUN-22             WEDNESDAY
30-JUN-22             THURSDAY
15-JUL-22             FRIDAY
29-JUL-22             SUNDAY
15-AUG-22             MONDAY
31-AUG-22             WEDNESDAY
15-SEP-22             THURSDAY
30-SEP-22             FRIDAY
14-OCT-22             SATURDAY
31-OCT-22             MONDAY
15-NOV-22             TUESDAY
30-NOV-22             WEDNESDAY
15-DEC-22             THURSDAY
30-DEC-22             SATURDAY

CodePudding user response：

Based on what you have so far:

WITH days AS
(
  SELECT days.column_value
    FROM TABLE(generate_dates_pipelined(DATE '2022-01-01', DATE '2022-12-31')) days
           LEFT JOIN holidays holy ON days.column_value = holy.holiday_date
          WHERE holy.holiday_date IS NULL
   WHERE TO_CHAR(days.column_value, 'DY') NOT IN ('SAT', 'SUN')
)
SELECT payment_day
  FROM (SELECT MAX(days.column_value) AS payment_day, TRUNC(days.column_value,'MM') 
          FROM days
         WHERE days.column_value <= TRUNC(days.column_value,'MM')   14
         GROUP BY TRUNC(days.column_value,'MM')
         UNION
        SELECT MAX(days.column_value) , TRUNC(days.column_value,'MM') 
          FROM days
         WHERE days.column_value <= ADD_MONTHS(TRUNC(days.column_value,'MM'),1) - 1
         GROUP BY TRUNC(days.column_value,'MM')
        )

I did some tests with this variant of mine:

WITH holidays AS
(
  SELECT TO_DATE('15-APR-2022 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AS dt,
         'Passover 2022' AS NAME
    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 
),
workdays AS
(
  SELECT TRUNC(wknd.dt) AS dt
    FROM (SELECT SYSDATE   ROWNUM - 1 dt
            FROM dual
           WHERE 1 = 1 /* you can limit those such as in your pipelined function here */
         CONNECT BY LEVEL <= 366) wknd
   WHERE TO_CHAR(wknd.dt, 'FMDAY', 'NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SUNDAY','SATURDAY')
)
SELECT payment_day
  FROM (SELECT MAX(dt) AS payment_day, TRUNC(dt,'MM')
          FROM (SELECT wd.*
                  FROM workdays wd
                       LEFT JOIN holidays hd ON wd.dt = hd.dt
                 WHERE hd.dt IS NULL) first_payroll
         WHERE first_payroll.dt <= TRUNC(first_payroll.dt,'MM')   14
         GROUP BY TRUNC(dt,'MM')
         UNION
        SELECT MAX(dt), TRUNC(dt,'MM')
          FROM (SELECT wd.*
                  FROM workdays wd
                       LEFT JOIN holidays hd ON wd.dt = hd.dt
                 WHERE hd.dt IS NULL) second_payroll
         WHERE second_payroll.dt <= ADD_MONTHS(TRUNC(second_payroll.dt,'MM'),1) - 1
         GROUP BY TRUNC(dt,'MM'));

Hope it gives you some useful ideas.