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.