добавьте n рабочих дней, включая настраиваемый список праздников в oracle plsql

Мне нужно найти 'n'th рабочий день в функции oracle plsql, которая должна исключать выходные и пользовательский список праздников. У меня есть что-то вроде этого, но я не могу вписать здесь логику

create or replace function add_n_working_days ( 
  start_date date, working_days pls_integer
) return date as
  end_date date := start_date;
  counter  pls_integer := 0;
begin

  if working_days = 0 then
    end_date := start_date;
  elsif to_char(start_date, 'fmdy') in ('sat', 'sun') then
    end_date := next_day(start_date, 'monday');
  end if;

  while (counter < working_days) loop
    end_date := end_date + 1;
    if to_char(end_date, 'fmdy') not in ('sat', 'sun') then
      counter := counter + 1;
    end if;
  end loop;

  return end_date;

end add_n_working_days;
/

У меня есть собственный список праздников в другой таблице, которая может быть извлечена с помощью SQL

select holidays from holiday_table

Я попытался добавить условие elsif с подзапросом, но это не поддерживается

if to_char(end_date, 'fmdy') not in ('sat', 'sun') then
      counter := counter + 1;
elsif to_char(end_date, 'YYYYMMDD') in (select holidays from holiday_table) then 
      counter := counter + 1;
end if;

Всего 3 ответа


Я попробую подсчитать количество выходных дней с помощью аналогичного цикла:

WHILE (v_copy > 0) LOOP
    end_date := end_date + 1;

    IF to_char(end_date, 'fmdy') IN ('sat', 'sun') THEN
         end_date := end_date + 1;
    ELSE
        v_copy := v_copy - 1;  
    END IF;

  END LOOP;

И затем, после завершения цикла, вы можете посчитать количество выходных в таблице, которые идут после start_date и end_date а не в ('sat', 'sun') .

SELECT COUNT(*) INTO v_custom_cnt 
 FROM holiday_table 
  WHERE to_char(holidays, 'fmdy') not in ('sat', 'sun') AND 
     holidays BETWEEN start_date AND end_date;

Теперь добавьте этот номер к своей end_date дате, и вы получите дату n-го рабочего дня. Конечно, перед возвратом проверьте, end_date ли новое значение end_date ('sat', 'sun') .

end_date := end_date + v_custom_cnt;

IF to_char(holidays, 'fmdy') in ('sat', 'sun') THEN
  end_date := next_day(end_date, 'monday');
END IF;

Наконец, end_date - это дата n-го рабочего дня.

CREATE OR REPLACE FUNCTION add_n_working_days ( 
  start_date DATE,
  working_days PLS_INTEGER
) RETURN DATE AS

  end_date DATE := start_date;
  counter  PLS_INTEGER := 0;
  v_copy PLS_INTEGER := working_days;

  v_custom_cnt INTEGER := 0;
BEGIN

  IF working_days = 0 THEN
    end_date := start_date;
  ELSIF to_char(start_date, 'fmdy') IN ('sat', 'sun') THEN
    end_date := next_day(start_date, 'monday');
  END IF;

  WHILE (v_copy > 0) LOOP
    end_date := end_date + 1;

    IF to_char(end_date, 'fmdy') IN ('sat', 'sun') THEN
         end_date := end_date + 1;
    ELSE
        v_copy := v_copy - 1;  
    END IF;

  END LOOP;

  SELECT COUNT(*) INTO  v_custom_cnt
    FROM holiday_table
  WHERE to_char(end_date, 'fmdy')  NOT IN ('sat', 'sun') 
    AND holidays BETWEEN start_date AND end_date;

  end_date := end_date + v_custom_cnt;

  IF to_char(end_date, 'fmdy') IN ('sat', 'sun') THEN
    end_date := next_day(start_date, 'monday');
  END IF;  


  RETURN end_date;

END add_n_working_days;

Это не проверено на 100%


Другой подход: создайте таблицу, в которой указан только рабочий день, и примите n-ное значение:

CREATE OR REPLACE FUNCTION add_n_working_days ( 
  start_date DATE, working_days PLS_INTEGER
) RETURN DATE AS
  l_end_date DATE := start_date;
  l_counter  pls_integer := 0;
BEGIN
  SELECT 
    business_day 
    INTO l_end_date
  FROM 
  (
    WITH 
    dates AS
      (SELECT start_date + level - 1  as dt FROM dual CONNECT BY level < 100)
    ,weekdates AS
    (SELECT dt as weekday FROM dates WHERE TO_CHAR(dt,'fmdy') NOT IN ('sat','sun'))
    ,business_days AS
    (
    SELECT weekday as business_day FROM weekdates
    MINUS
    SELECT holiday FROM so_holidays 
    )
    SELECT business_day, ROW_NUMBER() OVER (ORDER BY 1) as rn from business_days
  )
  WHERE rn = working_days + 1;
  RETURN l_end_date;
END add_n_working_days;

SQL ориентирован на множество, поэтому начните думать в терминах множеств, а не итераций (без циклов). Предполагая, что вы можете указать максимальное количество дней для тестирования, вы можете получить то, что вы хотите, с помощью одного простого запроса.

create or replace function add_n_working_days ( 
  start_date date, working_days pls_integer
) return date as
  working_date_ot date;
begin
    with date_list as (select trunc(start_date) + level - 1 tdate 
                 from dual 
                connect by level <= 5*working_days  
               )  
    select tdate
      into working_date_ot
      from ( select tdate, row_number() over(order by tdate) date_num
               from date_list
              where to_char(tdate,'fmdy') not in ('sat','sun') 
                and not exists 
                   (select null 
                      from holiday_dates
                     where trunc(holiday_date) = tdate
                   )
            )                  
    where date_num = working_days;

    return working_date_ot; 
end add_n_working_days; 

Как это работает: функция предполагает, что желаемая дата существует в 5 раз больше запрошенного количества дней. Таким образом, если будет запрошено 10 рабочих, итоговая дата будет в течение 50 дней (в противном случае это будет много выходных).

CTE date_list создает список потенциальных дат до установленного предела.
Подвыбор в основных фильтрах генерирует список, исключающий сб и вс. Затем он проверяет праздничную таблицу на оставшиеся даты и удаляет любую дату в таблице.
Оставшиеся даты нумеруются по возрастанию.
Затем основной внешний выбор выбирает номер даты, соответствующий заданным рабочим дням.

Цикл не требуется. Просто мои 0,02 цента стоит.


Есть идеи?

10000