计算工资日期

Question

我们采用双月制，员工在每月的15日和最后一天发工资。

如果这些日子是周六、周日或节假日，那么我们在此之前的最后一天(不是周六、周日或节假日)领取工资。

例如，以这个星期为例，4月15日是星期五，但它被定义为假日，所以人们应该在4月14日星期四发工资。

我设法得到一个部分查询工作，我可以排除周末和假期，但我可以使用一些帮助弄清楚什么日期的人应该支付。我的输出应该只包括发薪日。我想为本年度生产1月至12月的年度产出。

我在想也许last_day()是一个月的最后一个发薪日，一旦假期和周末被排除在外?

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

Answer 1

您可以使用查询生成日期:

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;

哪一个适合假期:

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;

输出:

2022-01-12(结婚)2022-01-31(星期一)2022-02-14(星期一)2022-02-28(星期一)2022-03-15(星期二)2022-03-31(星期四)2022-04-15(星期五)2022-04-29(星期五)2022-05-13(星期五)2022-05-31(星期二)2022-06-15(结婚)2022-06-30(星期四)2022-07-15(星期五)2022-07-29(星期五)2022-08-15(星期一)2022-08-31(结婚)2022-09-15(星期四)2022-09-30(星期五)2022-10-14(星期五)2022-10-31(星期一)2022-11-15(星期二)2022-11-30(结婚)2022-12-15(星期四)2022-12-30(星期五)

我认为我要遵循的基本思路是获得所有没有周末和节假日的日期，然后找到每个月的最大日期和每个月的最大日期<= 15日。

获取不包括周末和节假日的所有日期:

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

然后，从那里得到每个月unioned的最大日期，最大日期为<=每个月的15号。

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

在这个小提琴中有一个工作的例子，虽然我使用CTE来生成当前年份的所有日期(从这里借来)，因为你的generate_dates函数没有为我编译(但你可以只使用generate_dates)。

我想你的最终解决方案应该是这样的:

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