Calculate working days

C

Calculate working days for each month. You can create function from this and adjust your national holidays.

DECLARE
    last_month  VARCHAR2(8);
    last_count  PLS_INTEGER;
    --
    FUNCTION get_easter_monday (
        in_year PLS_INTEGER
    )
    RETURN DATE
    AS
        a NUMBER;
        c NUMBER;
        e NUMBER;
        g NUMBER;
        n NUMBER;
        x NUMBER;
    BEGIN
        c := in_year / 100 + 1;
        x := (3 * c / 4) - 12;
        g := MOD(in_year, 19) + 1;
        e := MOD(11 * g + 15 + ((8 * c + 5) / 25) - x, 30);
        --
        IF (e = 25 AND g < 11) OR e = 24 THEN
            e := e + 1;
        END IF;
        --
        n := 44 - e;
        IF n < 21 THEN
            n := n + 30;
        END IF;
        --
        a := n + 7 - MOD((5 * in_year / 4) - x - 3 + n, 7);
        --
        RETURN TRUNC(TO_DATE(TO_CHAR(in_year) || '0301', 'YYYYMMDD') + a);
    END;
    --
    FUNCTION is_business_day (
        in_date         DATE        := TRUNC(SYSDATE),
        in_weekends     PLS_INTEGER := 1,
        in_holidays     PLS_INTEGER := 1
    )
    RETURN NUMBER
    AS
        list_of_holidays CONSTANT VARCHAR2(256) := '0101,0501,0508,0705,0706,0928,1028,1117,1224,1225,1226';
    BEGIN
        -- check for weekends
        IF in_weekends = 1 AND TO_CHAR(in_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN') THEN
            -- MONDAY = 1 + TRUNC(in) - TRUNC(in, 'IW')
            RETURN -1;
        END IF;

        -- check for other holidays
        IF in_holidays = 1 THEN
            IF REGEXP_SUBSTR(list_of_holidays, TO_CHAR(in_date, 'MMDD')) IS NOT NULL THEN
                RETURN -2;
            END IF;
        END IF;

        -- check for easter monday
        IF in_holidays = 1 AND TO_CHAR(in_date, 'D-MM') IN ('1-03', '1-04') THEN
            IF in_date = get_easter_monday(TO_CHAR(in_date, 'YYYY')) THEN
                RETURN -3;
            END IF;
        END IF;
        --
        RETURN 1;
    END;
BEGIN
    -- year/month calendar overview with working days
    FOR c IN (
        SELECT
            TRUNC(SYSDATE, 'Y') + LEVEL - 1                     AS date_,
            TO_CHAR(TRUNC(SYSDATE, 'Y') + LEVEL - 1, 'MM/YYYY') AS month_
        FROM DUAL
        CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - TRUNC(SYSDATE, 'Y') + 1
    ) LOOP
        IF last_month != c.month_ OR last_month IS NULL THEN
            IF last_month IS NOT NULL THEN
                DBMS_OUTPUT.PUT_LINE(last_month || ' ' || last_count);
            END IF;
            --
            last_month := c.month_;
            last_count := 0;
        END IF;
        --
        last_count := last_count + CASE WHEN is_business_day(c.date_) > 0 THEN 1 ELSE 0 END;
        -- check what kind of holiday selected date is
        --DBMS_OUTPUT.PUT_LINE(TO_CHAR(c.date_, 'YYYY-MM-DD') || ' ' || NULLIF(is_business_day(c.date_), 1));
    END LOOP;
END;
/