时间戳

首页 > 解决方案 > 没有周末预言机功能的两个日期之间的差异

sql - 没有周末预言机功能的两个日期之间的差异

问题描述

我有一个函数，但它在 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;

标签： sqloracle

解决方案

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

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;

推荐阅读