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

CodePudding user response：

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>

CodePudding user response：

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