Oracle SQL计算OKPI剩余工作日：排除周日且延迟起始日期

需求背景 #

计算超KPI（OKPI）剩余天数，需满足两个核心规则：

• 不计周日为工作日
• 实际起始日期为bkg_date + 1，若该日期为周日则顺延至下周一（例：bkg_date = '08 Jul 2023'时，起始日为10 Jul 2023）

现有代码 #

CASE
    WHEN SYSDATE - (x.bkg_date + 1) <= x.dlvry_kpi THEN
        CASE
            WHEN x.dlvry_kpi - (
                -- Full weeks from Monday of start week to Monday of current week
                (TRUNC(SYSDATE, 'IW') - TRUNC(x.bkg_date + 1, 'IW')) * 6 / 7
                -- Add extra days in the current week excluding Sunday
                + CASE WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 <= 6 THEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 ELSE 6 END
                -- Subtract days in the week before the start date excluding Sunday
                - CASE WHEN TRUNC(x.bkg_date + 1) - TRUNC(x.bkg_date + 1, 'IW') <= 6 THEN TRUNC(x.bkg_date + 1) - TRUNC(x.bkg_date + 1, 'IW') ELSE 6 END
            ) <= 0 THEN '00 days left'
            ELSE
                TO_CHAR(
                    x.dlvry_kpi - (
                        -- Full weeks from Monday of start week to Monday of current week
                        (TRUNC(SYSDATE, 'IW') - TRUNC(x.bkg_date + 1, 'IW')) * 6 / 7
                        -- Add extra days in the current week excluding Sunday
                        + CASE WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 <= 6 THEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 ELSE 6 END
                        -- Subtract days in the week before the start date excluding Sunday
                        - CASE WHEN TRUNC(x.bkg_date + 1) - TRUNC(x.bkg_date + 1, 'IW') <= 6 THEN TRUNC(x.bkg_date + 1) - TRUNC(x.bkg_date + 1, 'IW') ELSE 6 END
                    )
                ) || ' days left'
        END
    ELSE 'OKPI'
END AS AGING

测试场景问题分析 #

输入参数：x.bkg_date = '08 Jul 2023'、sysdate = '17 Jul 2023'、x.dlvry_kpi = 7，现有代码返回'OKPI'，逻辑存在两处错误：

1. 第一层判断误用自然日差值：代码用SYSDATE - (x.bkg_date +1)计算自然日差（结果为8天），大于KPI的7天，直接触发OKPI；但实际工作日差应为7天（10-15日共6天，17日1天，排除周日16日），刚好等于KPI，应返回'00 days left'。
2. 未处理起始日为周日的顺延逻辑：用户例子中bkg_date+1是周日（09 Jul 2023），实际起始日应为周一（10 Jul 2023），但现有代码直接将bkg_date+1作为起始日，导致工作日计算基数错误。

优化方案 #

1. 修正起始日期计算 #

先处理bkg_date+1为周日的情况，顺延至下周一：

CASE 
    WHEN TRUNC(x.bkg_date + 1) = TRUNC(x.bkg_date + 1, 'IW') + 6 THEN x.bkg_date + 2
    ELSE x.bkg_date + 1
END AS actual_start_date

（注：TRUNC(date, 'IW')取ISO周的周一，周日为周一+6天，此写法无需依赖NLS语言设置）

2. 替换第一层判断为工作日差值计算 #

将重复的工作日计算逻辑封装，避免冗余，同时用工作日差值替代自然日差值做判断。

优化后完整代码 #

方案一：用CTE减少冗余

WITH order_info AS (
    SELECT 
        x.*,
        -- 计算实际起始日期
        CASE 
            WHEN TRUNC(x.bkg_date + 1) = TRUNC(x.bkg_date + 1, 'IW') + 6 THEN x.bkg_date + 2
            ELSE x.bkg_date + 1
        END AS actual_start_date
    FROM your_table x
)
SELECT 
    CASE
        -- 计算已用工作日天数
        WHEN (
            (TRUNC(SYSDATE, 'IW') - TRUNC(actual_start_date, 'IW')) * 6 / 7
            + CASE WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 <= 6 THEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 ELSE 6 END
            - CASE WHEN TRUNC(actual_start_date) - TRUNC(actual_start_date, 'IW') <= 6 THEN TRUNC(actual_start_date) - TRUNC(actual_start_date, 'IW') ELSE 6 END
        ) <= x.dlvry_kpi THEN
            CASE 
                WHEN x.dlvry_kpi - (
                    (TRUNC(SYSDATE, 'IW') - TRUNC(actual_start_date, 'IW')) * 6 / 7
                    + CASE WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 <= 6 THEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 ELSE 6 END
                    - CASE WHEN TRUNC(actual_start_date) - TRUNC(actual_start_date, 'IW') <= 6 THEN TRUNC(actual_start_date) - TRUNC(actual_start_date, 'IW') ELSE 6 END
                ) <= 0 THEN '00 days left'
                ELSE TO_CHAR(
                    x.dlvry_kpi - (
                        (TRUNC(SYSDATE, 'IW') - TRUNC(actual_start_date, 'IW')) * 6 / 7
                        + CASE WHEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 <= 6 THEN TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW') + 1 ELSE 6 END
                        - CASE WHEN TRUNC(actual_start_date) - TRUNC(actual_start_date, 'IW') <= 6 THEN TRUNC(actual_start_date) - TRUNC(actual_start_date, 'IW') ELSE 6 END
                    )
                ) || ' days left'
            END
        ELSE 'OKPI'
    END AS AGING
FROM order_info;

方案二：封装工作日计算为函数（更简洁）

CREATE OR REPLACE FUNCTION get_workdays(start_date DATE, end_date DATE) RETURN NUMBER IS
    total_days NUMBER;
BEGIN
    total_days := (TRUNC(end_date, 'IW') - TRUNC(start_date, 'IW')) * 6 / 7
                + CASE WHEN TRUNC(end_date) - TRUNC(end_date, 'IW') + 1 <= 6 THEN TRUNC(end_date) - TRUNC(end_date, 'IW') + 1 ELSE 6 END
                - CASE WHEN TRUNC(start_date) - TRUNC(start_date, 'IW') <= 6 THEN TRUNC(start_date) - TRUNC(start_date, 'IW') ELSE 6 END;
    RETURN GREATEST(total_days, 0); -- 避免出现负数
END;
/

-- 使用函数的查询
SELECT 
    CASE
        WHEN get_workdays(
            CASE WHEN TRUNC(x.bkg_date + 1) = TRUNC(x.bkg_date + 1, 'IW') + 6 THEN x.bkg_date + 2 ELSE x.bkg_date + 1 END,
            SYSDATE
        ) <= x.dlvry_kpi THEN
            CASE 
                WHEN x.dlvry_kpi - get_workdays(
                    CASE WHEN TRUNC(x.bkg_date + 1) = TRUNC(x.bkg_date + 1, 'IW') + 6 THEN x.bkg_date + 2 ELSE x.bkg_date + 1 END,
                    SYSDATE
                ) <= 0 THEN '00 days left'
                ELSE TO_CHAR(x.dlvry_kpi - get_workdays(
                    CASE WHEN TRUNC(x.bkg_date + 1) = TRUNC(x.bkg_date + 1, 'IW') + 6 THEN x.bkg_date + 2 ELSE x.bkg_date + 1 END,
                    SYSDATE
                )) || ' days left'
            END
        ELSE 'OKPI'
    END AS AGING
FROM your_table x;

测试验证 #

优化后代码针对测试场景：

• 实际起始日期为10 Jul 2023
• 计算工作日为7天（10-15日6天+17日1天），等于KPI=7，返回'00 days left'，符合预期。

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