时间戳

首页 > 解决方案 > 在oracle plsql中添加n个工作日，包括自定义假期列表

sql - 在oracle plsql中添加n个工作日，包括自定义假期列表

问题描述

我需要在 oracle plsql 函数中找到第 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;
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;

标签： sqloracleplsql

解决方案

不同的方法：创建一个只列出工作日并取第 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;

推荐阅读