CODEWITHSUNDEEP
sqloracle-database
RAHUL SONI
RAHUL SONI

Reputation: 1

Extract 5th Business Date from sysdate

I'm looking to extract the 5th Business Date data from database. Looking for pure 5th Business Date, no other business requirement like Holiday or New Year day.

Looking to extract 07/03/2022 from dual table using Oracle PL/SQL

Date Day Requirement
1/03/2022 Tuesday 1BD
2/03/2022 Wednesday 2BD
3/03/2022 Thursday 3BD
4/03/2022 Friday 4BD
5/03/2022 Saturday Weekend
6/03/2022 Sunday Weekend
7/03/2022 Monday 5BD
8/03/2022 Tuesday 6BD
9/03/2022 Wednesday 7BD

Upvotes: 0

Views: 117

Answers (2)

MT0
MT0

Reputation: 168416

The 5th business day will always be 7 days ahead, since there will be 5 weekdays and 2 weekend days, so the simplest solution is:

SELECT TRUNC(SYSDATE) + INTERVAL '7' DAYS
FROM   DUAL

More generally, if you want to add a number of business days to a date then you can calculate it using:

start_date
+ FLOOR(bd/5) * INTERVAL '7' DAY -- Full weeks
+ MOD(bd, 5) -- Part week
+ CASE
  WHEN start_date - TRUNC(start_date, 'IW') + MOD(bd, 5) >= 5
  THEN 2
  WHEN start_date - TRUNC(start_date, 'IW') + MOD(bd, 5) < 0
  THEN -2
  ELSE 0
  END -- Adjust for weekend

For example, given the sample data:

CREATE TABLE table_name (start_date, bd) AS
  SELECT TRUNC(SYSDATE), LEVEL - 11 FROM DUAL CONNECT BY LEVEL <= 21
UNION ALL
  SELECT DATE '2022-03-01', 5 FROM DUAL;

Then:

SELECT start_date,
       bd,
       start_date
       + FLOOR(bd/5) * INTERVAL '7' DAY -- Full weeks
       + MOD(bd, 5) -- Part week
       + CASE
         WHEN start_date - TRUNC(start_date, 'IW') + MOD(bd, 5) >= 5
         THEN 2
         WHEN start_date - TRUNC(start_date, 'IW') + MOD(bd, 5) < 0
         THEN -2
         ELSE 0
         END -- Adjust for weekend
         AS adjusted_business_day
FROM   table_name;

Outputs:

START_DATE BD ADJUSTED_BUSINESS_DAY
2022-03-24 00:00:00 (THU) -10 2022-03-10 00:00:00 (THU)
2022-03-24 00:00:00 (THU) -9 2022-03-04 00:00:00 (FRI)
2022-03-24 00:00:00 (THU) -8 2022-03-07 00:00:00 (MON)
2022-03-24 00:00:00 (THU) -7 2022-03-08 00:00:00 (TUE)
2022-03-24 00:00:00 (THU) -6 2022-03-09 00:00:00 (WED)
2022-03-24 00:00:00 (THU) -5 2022-03-17 00:00:00 (THU)
2022-03-24 00:00:00 (THU) -4 2022-03-11 00:00:00 (FRI)
2022-03-24 00:00:00 (THU) -3 2022-03-14 00:00:00 (MON)
2022-03-24 00:00:00 (THU) -2 2022-03-15 00:00:00 (TUE)
2022-03-24 00:00:00 (THU) -1 2022-03-16 00:00:00 (WED)
2022-03-24 00:00:00 (THU) 0 2022-03-24 00:00:00 (THU)
2022-03-24 00:00:00 (THU) 1 2022-03-25 00:00:00 (FRI)
2022-03-24 00:00:00 (THU) 2 2022-03-28 00:00:00 (MON)
2022-03-24 00:00:00 (THU) 3 2022-03-29 00:00:00 (TUE)
2022-03-24 00:00:00 (THU) 4 2022-03-30 00:00:00 (WED)
2022-03-24 00:00:00 (THU) 5 2022-03-31 00:00:00 (THU)
2022-03-24 00:00:00 (THU) 6 2022-04-01 00:00:00 (FRI)
2022-03-24 00:00:00 (THU) 7 2022-04-04 00:00:00 (MON)
2022-03-24 00:00:00 (THU) 8 2022-04-05 00:00:00 (TUE)
2022-03-24 00:00:00 (THU) 9 2022-04-06 00:00:00 (WED)
2022-03-24 00:00:00 (THU) 10 2022-04-07 00:00:00 (THU)
2022-03-01 00:00:00 (TUE) 5 2022-03-08 00:00:00 (TUE)

db<>fiddle here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143023

This is how I understood it.

Today is Thursday, 24.03.2022. It means that 5th business day looking backwards is Friday, 18.03.2022.

SQL> with test (datum, day) as
  2    -- calendar
  3    (select
  4                    trunc(sysdate) - &&par_number_of_days * 2 + level - 1,
  5            to_char(trunc(sysdate) - &&par_number_of_days * 2 + level - 1, 'dy',
  6                    'nls_date_language = english')
  7     from dual
  8     connect by level <= (&&par_number_of_days * 2) + 1
  9    ),
 10  only_working_days as
 11    -- remove weekends
 12    (select datum,
 13            day,
 14            row_number() over (order by datum desc) rn
 15     from test
 16     where day not in ('sat', 'sun')
 17    )
 18  select datum, day, rn
 19  from only_working_days
 20  where rn = &&par_number_of_days;
Enter value for par_number_of_days: 5

DATUM      DAY         RN
---------- --- ----------
18.03.2022 fri          5

Or, 13th business day backwards is 08.03.2022:

SQL> undefine par_number_of_days
SQL> /
Enter value for par_number_of_days: 13

DATUM      DAY         RN
---------- --- ----------
08.03.2022 tue         13

SQL>

If it is, on the other hand, related to period since 1st of current, month, then

SQL> with test (datum, day) as
  2    (select trunc(sysdate, 'mm') + level - 1,
  3            to_char(trunc(sysdate, 'mm') + level - 1, 'dy', 'nls_date_language = english')
  4     from dual
  5     connect by level <= trunc(sysdate) - trunc(sysdate, 'mm') + 1
  6    ),
  7  only_working_days as
  8    -- remove weekends
  9    (select datum,
 10            day,
 11            row_number() over (order by datum) rn
 12     from test
 13     where day not in ('sat', 'sun')
 14    )
 15  select datum, day, rn
 16  from only_working_days
 17  where rn = &par_number_of_days;
Enter value for par_number_of_days: 5

DATUM      DAY         RN
---------- --- ----------
07.03.2022 mon          5

SQL> /
Enter value for par_number_of_days: 13

DATUM      DAY         RN
---------- --- ----------
17.03.2022 thu         13

SQL>

Upvotes: 1

Related Questions