FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)'
/

CREATE TABLE table_name (dt, days) AS
SELECT TRUNC(SYSDATE, 'IW') + 0, -3 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 2.5, -3 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 2, -1 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 3, 2 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 2, 7 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 5.5, 0 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 5.5, 7 FROM DUAL UNION ALL
SELECT TRUNC(SYSDATE, 'IW') + 0.2, 5 FROM DUAL
/

WITH FUNCTION AMS_SUBTRACT_WORKDAY(VDATE IN DATE, VDAYS IN NUMBER)
    RETURN DATE
    IS
    VENDD DATE;
    VDYWR NUMBER := 5;
    VDYCL NUMBER := 7;
    VDAYD NUMBER;
    VDAY2 NUMBER := VDAYS;
    VWEKS NUMBER;
    BEGIN
        SELECT TO_NUMBER(VDATE - TRUNC(VDATE, 'IW')) INTO VDAYD FROM DUAL;
        IF VDAYD > VDAY2 THEN 
            VENDD := VDATE - VDAY2;
        ELSE 
            VDAY2 := VDAY2 - VDAYD;
            VENDD := TRUNC(VDATE, 'IW');
            VENDD := VENDD - (TRUNC(VDAY2 / 5) * 7) - 2;
            VDAY2 := VDAY2 - (TRUNC(VDAY2 / 5) * 5);
            VENDD := VENDD - VDAY2;
        END IF;
        RETURN VENDD;
    END AMS_SUBTRACT_WORKDAY;
SELECT dt,
       days,
       AMS_SUBTRACT_WORKDAY(dt, -days) RES,
       -- Start of Week
       TRUNC(dt, 'IW')
       -- Add Full Business Weeks (multiples of 5 days)
       + FLOOR((LEAST(dt - TRUNC(dt, 'IW'), 5) + days)/5)
         * INTERVAL '7' DAY
       -- Add Part Business Weeks  
       + LEAST(dt - TRUNC(dt, 'IW'), 5) + days
       - FLOOR((LEAST(dt - TRUNC(dt, 'IW'), 5) + days)/5) * 5
         AS offset_day
FROM   table_name
/

RES
2024-09-21 00:00:00 (SAT)

DT	DAYS	RES	OFFSET_DAY
2024-09-23 00:00:00 (MON)	-3	2024-09-18 00:00:00 (WED)	2024-09-18 00:00:00 (WED)
2024-09-25 12:00:00 (WED)	-3	2024-09-20 12:00:00 (FRI)	2024-09-20 12:00:00 (FRI)
2024-09-25 00:00:00 (WED)	-1	2024-09-24 00:00:00 (TUE)	2024-09-24 00:00:00 (TUE)
2024-09-26 00:00:00 (THU)	2	2024-09-28 00:00:00 (SAT)	2024-09-30 00:00:00 (MON)
2024-09-25 00:00:00 (WED)	7	2024-10-02 00:00:00 (WED)	2024-10-04 00:00:00 (FRI)
2024-09-28 12:00:00 (SAT)	0	2024-09-28 12:00:00 (SAT)	2024-09-30 00:00:00 (MON)
2024-09-28 12:00:00 (SAT)	7	2024-10-05 12:00:00 (SAT)	2024-10-09 00:00:00 (WED)
2024-09-23 04:48:00 (MON)	5	2024-09-28 04:48:00 (SAT)	2024-09-30 04:48:00 (MON)