Oracle SQL计算合规发薪日期：年度半月制发薪日生成需求

需求说明 #

• 薪资规则：半月制发薪，每月固定以15日和月末为基准发薪日
• 调整规则：若基准发薪日为周六、周日或节假日，需提前至最近的非周末、非节假日日期
• 示例：2022年4月15日为周五但属于节假日，最终发薪日调整为4月14日（周四）
• 目标：生成当年1-12月的所有合规发薪日，确认是否可通过last_day()处理月末发薪日的调整

实现方案 #

可以利用last_day()准确获取每月月末日期作为基准，再通过向前遍历的方式，为每个基准日匹配符合要求的合规发薪日。以下是完整的Oracle SQL实现：

步骤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;

步骤2：生成合规发薪日的查询语句 #

WITH monthly_base_dates AS (
    -- 生成当年1-12月的两个基准发薪日：15日和月末
    SELECT
        TRUNC(SYSDATE, 'YYYY') + (LEVEL - 1)*30 + 14 AS base_date,
        '15日基准' AS type
    FROM DUAL
    CONNECT BY LEVEL <= 12
    UNION ALL
    SELECT
        LAST_DAY(TRUNC(SYSDATE, 'YYYY') + (LEVEL - 1)*30) AS base_date,
        '月末基准' AS type
    FROM DUAL
    CONNECT BY LEVEL <= 12
),
adjusted_dates AS (
    -- 对每个基准日，向前遍历最多7天，筛选合规日期
    SELECT
        base_date,
        type,
        TRUNC(base_date - rn) AS check_date,
        -- 判断当前日期是否为合规工作日
        CASE
            WHEN TO_CHAR(base_date - rn, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
                 AND NOT EXISTS (SELECT 1 FROM holidays h WHERE h.holiday_date = TRUNC(base_date - rn))
            THEN TRUNC(base_date - rn)
        END AS valid_pay_date
    FROM monthly_base_dates
    CROSS JOIN (SELECT LEVEL - 1 AS rn FROM DUAL CONNECT BY LEVEL <= 7) -- 最多向前检查7天，覆盖极端情况
)
-- 提取每个基准日对应的第一个合规发薪日并排序
SELECT DISTINCT
    TO_CHAR(valid_pay_date, 'YYYY-MM-DD') AS 合规发薪日,
    type AS 基准类型
FROM adjusted_dates
WHERE valid_pay_date IS NOT NULL
ORDER BY valid_pay_date;

关键说明 #

• last_day()函数可以精准获取每月最后一天，完美适配月末基准发薪日的需求
• 通过CROSS JOIN生成0-6的日期偏移量，向前遍历最多7天，确保覆盖周末（2天）加连续节假日的极端场景
• 结合节假日表和周末判断逻辑，筛选出合规日期，最后通过DISTINCT确保每个基准日只返回一个有效发薪日

内容的提问来源于stack exchange，提问作者Pugzly