ylliX - Online Advertising Network
Vertex AI - Antrophic and Mistral models: Why does it require Imegen access?

Adjust the date as working date | Oracle |


I have refering a lot of articales on stack i came across one query worked fine for me as expected
But need some modification to make it work for me.

Need to considering printing MONDAY TO FRIDAY dates only. SATURDAY and SUNDAY with + or – should give me only Working dates.

Case 1 : When date is 13-APR-2024 which is (SATURDAY) doing 0 or -1 it should give me FRIDAY date 12-APR-2024 which is working date.

Case 2 : When date is 13-OCT-2024 which is (SUNDAY) doing 0 or -1 it should give me FRIDAY date 11-OCT-2024 which is working date.

Case 3 : When date is 08-OCT-2024 which is (TUESDAY) doing +4 or +5 (SATURDAY 12-OCT-2024 / SUNDAY 13-OCT-2024) it should give me FRIDAY date 11-OCT-2024 which is working date.

**Below is table **

Table :

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;

Query :

SELECT dt,
       days,
       -- 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;

Expecting ….

Now here is the catch need to be fixed. Help some guide how to correct above query.



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *