Oracle calculate pay DATE

Question

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

Answer 1

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;



WITH  parameters (first_month, num_months)  AS
(
    SELECT TO_DATE ('JAN-2022', 'MON-YYYY')    -- First month wanted
    ,    4                       -- Total number of months
    FROM    dual
)
,  all_scheduled_paydays  AS
(
    SELECT  ADD_MONTHS (first_month + 14,       LEVEL - 1) AS payday_1
    ,    ADD_MONTHS (LAST_DAY (first_month), LEVEL - 1) AS payday_2
    FROM    parameters
    CONNECT BY LEVEL <= num_months
)
SELECT  c.actual_payday
,     a.scheduled_payday    -- For debugging
FROM     all_scheduled_paydays
UNPIVOT  ( scheduled_payday
     FOR col IN (payday_1, payday_2)
     ) a
CROSS APPLY (
               SELECT a.scheduled_payday + 1 - LEVEL AS actual_payday
        FROM    dual
        WHERE    CONNECT_BY_ISLEAF = 1
        CONNECT BY TO_CHAR ( a.scheduled_payday + 2 - LEVEL
                    , 'DY'
                  , 'NLS_DATE_LANGUAGE=ENGLISH'
                  ) IN ('SAT', 'SUN')
            OR  a.scheduled_payday + 2 - LEVEL
               IN (
                     SELECT holiday_date
                  FROM  holidays
                   )
         ) c
ORDER BY scheduled_payday
;

Answer 2

You can use below to generate the date calendar that excludes Saturday and Sunday. Holiday list is built and filtered.

Main Query

with cte_h as
(
select to_date('15-APR-2022','DD-MON-YYYY') h_dt, 
'PASSOVER' h_name from dual union all
select to_date('31-DEC-2022','DD-MON-YYYY') h_dt, 
'New Year eve' h_name from dual
)
,cte_cal as (
select
decode(mod(level-1,2),
0,
case 
when add_months(trunc(sysdate,'YEAR'),ceil(level/2)-1)+14 
IN
(select h_dt from cte_h) 
then add_months(trunc(sysdate,'YEAR'),ceil(level/2)-1)+13
else add_months(trunc(sysdate,'YEAR'),ceil(level/2)-1)+14 END,
case 
when last_day(add_months(trunc(sysdate,'YEAR'),ceil(level/2)-1)) 
IN
(select h_dt from cte_h) 
then last_day(add_months(trunc(sysdate,'YEAR'),ceil(level/2)-1)) - 1 
else last_day(add_months(trunc(sysdate,'YEAR'),ceil(level/2)-1)) END ) day
from dual
connect by level < 25),
cte_pd as 
(select 
DAY as date_orig_w_h_adj,
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 pay_date, to_char(day,'DAY') dow_orig
from cte_cal)
select * from cte_pd;

Output from above -

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

24 rows selected.

Answer 3

Once you build the calendar, and the holiday table, you need to refer to them twice, once to determine the 'nominal' pay dates (15th and last day of each month) and determine if this is a holiday or weekend, and another time to find the most recent working day prior to that date:

With Cal as
  (SELECT TRUNC (SYSDATE - ROWNUM) dt
   FROM DUAL CONNECT BY ROWNUM < 366),
PayCal as (
  select c.dt as THIS_DATE
        , CASE WHEN h.holiday_date is not null then 'Y' else 'N' end as IS_HOLIDAY
        , CASE WHEN to_char(dt, 'DY') IN ('SAT', 'SUN') then 'Y' else 'N' end as IS_SAT_OR_SUN
  from Cal c

    left join
      -- determine if c is a public holiday
        holidays h
        on  c.dt = h.holiday_date
)
select 
    c.THIS_DATE
    , case when IS_HOLIDAY='Y' or IS_SAT_OR_SUN='Y' then
                   (select max(c1.THIS_DATE) 
                    from PayCal c1
                    where c1.THIS_DATE < c.THIS_DATE  
                    and c1.IS_HOLIDAY='N'
                    and c1.IS_SAT_OR_SUN='N')
                else c.THIS_DATE
          end as PAY_ON_THIS_DATE
from PayCal c
where EXTRACT(DAY FROM c.THIS_DATE)=15
      OR
      LAST_DAY(c.THIS_DATE)=c.THIS_DATE

I have used a version of calendar data generation logic I found in SO, but you can substitute my Cal table with your looping-function.

Answer 4

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.