没有周末预告片功能的两个日期之间存在差异

debugcn 发表于 Dev

布莱斯

我有一个函数，但是它在oracle数据库的两个日期之间返回错误的数字。问题是当我在同一天有两个约会时。函数仅使用COUNT（）函数来计算工作日数，因此只能将其取整。

如何修复此功能？

CREATE OR REPLACE
FUNCTION fn_GetBusinessDaysInterval
(
    v_Begin_Date DATE,
    v_End_Date DATE   
)
    RETURN NUMBER
AS
    v_DaysInbetween NUMBER := 0;
    v_BusDaysInbetween NUMBER := 0;

BEGIN
    WITH days
    AS
    (
        SELECT
            v_Begin_Date + seq AS day_date,
            to_char(v_Begin_Date + seq , 'D') day_of_week
        FROM
        (
            SELECT ROWNUM-1 seq
            FROM   ( SELECT 1
                     FROM   dual
                     --number of rows should be exactly the number of days between begin and end dates
                     CONNECT BY LEVEL <= (v_End_Date - v_Begin_Date) + 1
                   )
        )
        ORDER BY 1
    )
    SELECT
        v_End_Date - v_Begin_Date AS days_inbetween,
        count(1) business_days_inbetween
    INTO
        v_DaysInbetween,
        v_BusDaysInbetween        
    FROM
        days
    WHERE
        ---------------------------------------------------
        --...then exclude Sat and Sun
        ---------------------------------------------------
        days.day_of_week NOT IN (7,1)
        ---------------------------------------------------
        --...then exclude week-day holidays
        ---------------------------------------------------

        AND NOT EXISTS
            (
                SELECT SWIETO
                FROM DNI_SWIATECZNE ht
                WHERE ht.DATA = days.day_date
                --Optionally include Region!
            );        



    RETURN v_BusDaysInbetween;        
END;

大力水手

我认为您需要考虑以下代码：

CREATE OR REPLACE FUNCTION FN_GETBUSINESSDAYSINTERVAL (
    V_BEGIN_DATE DATE,
    V_END_DATE DATE
) RETURN NUMBER AS
    V_DAYSINBETWEEN      NUMBER := 0;
    V_BUSDAYSINBETWEEN   NUMBER := 0;
BEGIN
    WITH DAYS AS (
        SELECT V_BEGIN_DATE + LEVEL AS DAY_DATE,
               TO_CHAR(V_BEGIN_DATE + LEVEL, 'D') DAY_OF_WEEK
          FROM DUAL
        CONNECT BY LEVEL <= ( V_END_DATE - V_BEGIN_DATE )
    )
    SELECT
        V_END_DATE - V_BEGIN_DATE AS DAYS_INBETWEEN, --total number of days in between
        SUM(CASE WHEN DAYS.DAY_OF_WEEK NOT IN( 7, 1 )
                 AND HT.DATA IS NULL THEN 1 END) BUSINESS_DAYS_INBETWEEN -- total number of business days inbetween
    INTO V_DAYSINBETWEEN, V_BUSDAYSINBETWEEN
    FROM DAYS LEFT
         JOIN DNI_SWIATECZNE HT ON HT.DATA = DAYS.DAY_DATE;

    RETURN COALESCE(V_BUSDAYSINBETWEEN, 0);

EXCEPTION 
    WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。