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, Sunday or holiday, then we get paid on the last day before then that isn't a Saturday, Sunday or 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.

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. I would like to produce a years output Jan-dec for the current year.

I was thinking perhaps last_day() for the last pay day of the month once holidays and weekends were excluded?

CREATE OR REPLACE FUNCTION generate_dates(i_from_date IN DATE, i_end_date IN DATE, i_min_delta IN NUMBER, i_max_delta IN NUMBER, i_num_rows IN NUMBER) 
RETURN VARCHAR2 
SQL_MACRO 
IS 
BEGIN 
RETURN q'{
SELECT start_date, end_date
   FROM ( 
        SELECT pivot_date AS start_date, pivot_date   NUMTODSINTERVAL( i_min_delta   (i_max_delta-i_min_delta) * DBMS_RANDOM.VALUE(), 'hour') AS end_date 
        FROM (
            SELECT pivot_date   DBMS_RANDOM.VALUE() AS pivot_date            
            FROM ( 
                SELECT rownum AS rn, pivot_date AS pivot_date FROM ( 
                    SELECT TRUNC(i_from_date) level-1 AS pivot_date FROM DUAL 
                    CONNECT BY TRUNC(i_from_date) level-1<=TRUNC(i_end_date) 
                ) 
            ) 
            CONNECT BY LEVEL <= i_num_rows AND PRIOR rn = rn AND PRIOR sys_guid() IS NOT NULL 
        )
    ) 
}' ; 
END;
/

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
        c.dt,
       to_char(c.dt, 'DY') as dow
FROM generate_dates(
TIMESTAMP '2022-01-01 00:00:00',
TIMESTAMP '2022-04-30 00:00:00',
 1, 'DAY') c
where 
to_char(c.dt, 'DY') NOT IN ('SAT', 'SUN') 
AND NOT EXISTS (
             SELECT 1
             FROM   holidays h
             WHERE  c.dt = h.holiday_date
           );

CodePudding user response：

I think the basic idea I would follow is getting all the dates without weekends and holidays, then finding the maximum day of each month and the maximum day of each month that is <= the 15th.

Get all dates without weekends and holidays:

WITH noholidays AS
(
SELECT dt
FROM 
 generate_dates(
  TIMESTAMP '2022-01-01 00:00:00',
  TIMESTAMP '2022-04-30 00:00:00',
  1, 'DAY') c
WHERE to_char(dt, 'DY') NOT IN ('SAT', 'SUN')
 AND dt NOT IN (SELECT holiday_date FROM holidays)
)

Then, from there, get the maximum date for each month unioned with the maximum date that is <= the 15th of each month.

 SELECT max(dt) payday
 FROM noholidays
 GROUP BY to_char(dt, 'YYYY-MM')
UNION ALL
 SELECT max(dt)
 FROM noholidays
 WHERE to_number(to_char(dt, 'DD')) <= 15
 GROUP BY to_char(dt, 'YYYY-MM')
ORDER BY payday

There's a working example in this fiddle, although I used a CTE to generate all the dates in the current year (borrowed from here) since your generate_dates function didn't compile for me (but you can just use generate_dates).

I think your final solution would look something like:

WITH 
noholidays AS (
 SELECT dt
 FROM generate_dates(
  TIMESTAMP '2022-01-01 00:00:00',
  TIMESTAMP '2022-04-30 00:00:00',
  1, 'DAY')
 WHERE to_char(dt, 'DY') NOT IN ('SAT', 'SUN')
  AND dt NOT IN (SELECT holiday_date FROM holidays)
)
SELECT max(dt) payday
FROM noholidays
GROUP BY to_char(dt, 'YYYY-MM')
UNION ALL
SELECT max(dt)
FROM noholidays
WHERE to_number(to_char(dt, 'DD')) <= 15
GROUP BY to_char(dt, 'YYYY-MM')
ORDER BY payday

CodePudding user response：

You can use:

WITH pay_dates (dt) AS (
  SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YY'), LEVEL) - INTERVAL '1' DAY
  FROM DUAL
  CONNECT BY LEVEL <= 12
UNION ALL
  SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YY'), LEVEL - 1)   INTERVAL '14' DAY
  FROM DUAL
  CONNECT BY LEVEL <= 12
),
skip_weekends (dt) AS (
  SELECT CASE dt - TRUNC(dt, 'IW')
         WHEN 6 THEN dt - 2 -- Sunday
         WHEN 5 THEN dt - 1 -- Saturday
         ELSE        dt     -- Weekday
         END
  FROM   pay_dates
),
skip_holidays (dt, holiday_date) AS (
  SELECT w.dt, h.holiday_date
  FROM   skip_weekends w
         LEFT OUTER JOIN holidays h
         ON (w.dt = h.holiday_date)
UNION ALL
  SELECT CASE s.dt - TRUNC(s.dt, 'IW')
         WHEN 0
         THEN s.dt - 3 -- Monday
         ELSE s.dt - 1 -- Other weekday
         END,
         h.holiday_date
  FROM   skip_holidays s
         LEFT OUTER JOIN holidays h
         ON ( CASE s.dt - TRUNC(s.dt, 'IW')
              WHEN 0
              THEN s.dt - 3
              ELSE s.dt - 1
              END = h.holiday_date )
  WHERE  s.holiday_date IS NOT NULL
)
SELECT dt
FROM   skip_holidays
WHERE  holiday_date IS NULL
ORDER BY dt;

Which for the holidays:

CREATE TABLE holidays (holiday_date) AS
SELECT TRUNC(SYSDATE, 'YY')   INTERVAL '12' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'YY')   INTERVAL '13' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'YY')   INTERVAL '14' DAY FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'YY')   INTERVAL '45' DAY FROM DUAL;

Outputs:

fiddle