How to create a statement for subtract a date omitting weekends and holidays in Oracle PLSQL-CodePudding

I need to make a query where from a final date specific days are subtracted, omitting weekends (Saturday and Sunday) and holidays, Once the operation is done, it proceeds to display the date resulting from that operation, for example:

(3/1/2023 - 7) = 2/20/2023

On March 3, 7 days are subtracted, if I apply that statement it should show 2/23/2023 but since within that operation it went through Saturday and Sunday, it must omit them and continue subtracting as long as the days are between Monday to Friday. I've a table where I already have the festive dates, I would only check if there is also a festive date, it is omitted and continues subtracting.

Is possible to create that statement?

CodePudding user response：

Here's one option; it uses holidays table (which you have) and a function (which creates a calendar, marks weekends and holidays and skips them in a loop).

Holidays:

SQL> select * from holidays order by datum;

DATUM
----------
01.11.2022
25.12.2022
26.12.2022
01.01.2023
06.01.2023

Function:

SQL> create or replace function f_result
  2    (par_datum in date, par_number_of_days in number)
  3    return date
  4  is
  5    retval date   := par_datum;
  6    i      number := 0;
  7  begin
  8    for cur_r in
  9      -- calendar
 10      (with
 11       temp (datum) as
 12        (select par_datum - level   1
 13         from dual
 14         connect by level <= par_number_of_days   20
 15        )
 16        -- mark weekends and holidays
 17        select t.datum,
 18            case when to_char(t.datum, 'dy') in ('sat', 'sun') then 1 else 0 end cb_weekend,
 19            case when t.datum = h.datum then 1 else 0 end cb_holiday
 20          from temp t left join holidays h on h.datum = t.datum
 21          order by t.datum desc
 22      ) loop
 23        retval := cur_r.datum;
 24        -- skip weekends and holidays
 25        i := i   case when cur_r.cb_weekend = 1 or cur_r.cb_holiday = 1 then 0 else 1 end;
 26        exit when i > par_number_of_days;
 27    end loop;
 28    return retval;
 29  end;
 30  /

Function created.

Calendar (January 2023; names are in Croatian, but disregard that. Weekends and holidays are marked in different color. Today's date is 20.01.2023 (dd.mm.yyyy)):

Testing:

SQL> select f_result(date '2023-01-20', 10) from dual;

F_RESULT(D
----------
05.01.2023

SQL> select f_result(date '2023-01-10', 7) from dual;

F_RESULT(D
----------
29.12.2022

SQL>

CodePudding user response：

declare
  v_result_date date := to_date('03/01/2023','mm/dd/yyyy');
  v_days number := 7;
begin 
 while v_days > 0
  loop

    if to_char(v_result_date,'dy') not in ('sat','sun')
    then
      v_days := v_days - 1;
    end if;   
    v_result_date := v_result_date - 1;

  end loop;
  dbms_output.put_line(v_result_date);
end;