如何在Oracle PLSQL中创建一条语句来减去省略周末和假日的日期

pxy2qtax 于 2023-01-25 发布在 Oracle

关注(0)|答案(3)|浏览(283)

我需要进行一个查询，从最终日期减去特定的天数，忽略周末（星期六和星期日）和假日。一旦操作完成，它将继续显示该操作产生的日期，例如：

（2023年3月1日至7日）= 2023年2月20日

在3月3日，减去了7天，如果我应用该语句，它应该显示2/23/2023，但由于在该操作中，它经过了星期六和星期日，它必须忽略它们，并继续减去，只要日期在星期一到星期五之间。我有一个表，其中已经有节日日期，我只会检查是否还有节日日期，它被忽略，并继续减去。
有可能创造这样的陈述吗？

oracle

来源：https://stackoverflow.com/questions/75189593/how-to-create-a-statement-for-subtract-a-date-omitting-weekends-and-holidays-in

3条答案

按热度按时间

2skhul331#

这里有一个选择它使用假日表（你有）和一个函数（它创建一个日历，标记周末和假日，并在循环中跳过它们）。
假期：

SQL> select * from holidays order by datum;

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

功能：

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.

日历（2023年1月;名字是克罗地亚语，但忽略它。周末和节假日用不同的颜色标记。今天的日期是20.01.2023（dd. mm. yyyy））：

测试：

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>

如果您想 * 向前 * 移动，请稍微更改函数代码：

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
 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.

例如：

SQL> select f_result(date '2022-12-30', 1) res_1,
  2         f_result(date '2023-01-04', 7) res_2,
  3         f_result(date '2023-03-03', 7) res_3,
  4         f_result(date '2023-03-01', 7) res_4
  5  from dual;

RES_1      RES_2      RES_3      RES_4
---------- ---------- ---------- ----------
02.01.2023 16.01.2023 14.03.2023 10.03.2023

SQL>

具体而言，您在评论中提到的RES_3（截至2023年1月3日+7天）为：

03.01.2023 + 7 = 
  day 1: 04.01.
  day 2: 05.01.
  skip   06.01. (holiday), 07.01. and 08.01. (weekend)
  day 3: 09.01.
  day 4: 10.01.
  day 5: 11.01.
  day 6: 12.01.
  day 7: 13.01.  --> RES_3

赞(0）回复(0）举报 2023-01-25

oogrdqng2#

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;

赞(0）回复(0）举报 2023-01-25

s8vozzvw3#

这可以通过使用model子句的SQL来实现。你可以试试这个。
节假日和日期2023年3月1日的样本数据，包括7天的倒计时（考虑到周末和节假日）：

WITH
    holydays (A_DATE) AS
        (
            Select DATE '2023-01-01' From Dual Union All
            Select DATE '2023-01-06' From Dual Union All
            Select DATE '2023-04-09' From Dual Union All
            Select DATE '2023-04-10' From Dual Union All
            Select DATE '2023-06-08' From Dual Union All
            Select DATE '2023-06-22' From Dual Union All
            Select DATE '2023-08-05' From Dual Union All
            Select DATE '2023-08-15' From Dual Union All
            Select DATE '2023-11-01' From Dual Union All
            Select DATE '2023-11-18' From Dual Union All
            Select DATE '2023-12-25' From Dual Union All
            Select DATE '2023-12-26' From Dual 
        ),
    test_period AS
        (
          Select To_Date('2023-03-01', 'yyyy-mm-dd') "REF_DATE", 7 "NUM_OF_DAYS" From Dual 
        ),

现在，使用Oracle SQL MODEL子句，我们可以创建返回数据集的CTE，该数据集将具有一组相反的日期（不包括周末和节假日）。

days AS
    (   SELECT    IDX, REF_DATE, ROWNUM - 1 "RN", DAY_OF_WEEK, NUM_OF_DAYS 
        FROM   (  Select      IDX, REF_DATE, NUM_OF_DAYS, 
                              CASE WHEN Nvl(To_Char(A_DATE), 'N') = 'N' And
                                        Case When SubStr(To_Char(REF_DATE, 'Day'), 1, 3) IN('Sat', 'Sun') Then 'Y' Else 'N' End = 'N'
                                    THEN 1
                              END "DAYS_PAST",
                              To_Char(REF_DATE, 'Day') "DAY_OF_WEEK",
                              A_DATE,
                              Nvl(To_Char(A_DATE), 'N')  "IS_HOLYDAY",
                              Case When SubStr(To_Char(REF_DATE, 'Day'), 1, 3) IN('Sat', 'Sun') Then 'Y' Else 'N' End "IS_WEEKEND"
                  From    (   Select  REF_DATE, NUM_OF_DAYS  From test_period   ) p
                  Left Join   holydays h ON(h.A_DATE = REF_DATE)
                        MODEL
                            Dimension By        (0 as IDX)
                            Measures              (REF_DATE, NUM_OF_DAYS, A_DATE, 0 AS DAYS_PAST)
                              RULES     ITERATE(100)   -- NUMBER OF ITERATIONS SHOULD BE BIGGER TO COVER SKIPPED DAYS
                                  (
                                    REF_DATE[ITERATION_NUMBER] = REF_DATE[0] - ITERATION_NUMBER,
                                    NUM_OF_DAYS[ITERATION_NUMBER] = NUM_OF_DAYS[0]
                                  )
                  Order by REF_DATE DESC
              )
        WHERE DAYS_PAST Is Not Null And ROWNUM <= NUM_OF_DAYS + 1
    )

  R e s u l t :
       IDX REF_DATE          RN DAY_OF_WEEK NUM_OF_DAYS
---------- --------- ---------- ----------- -----------
         0 01-MAR-23          0 Wednesday             7 
         1 28-FEB-23          1 Tuesday               7 
         2 27-FEB-23          2 Monday                7 
         5 24-FEB-23          3 Friday                7 
         6 23-FEB-23          4 Thursday              7 
         7 22-FEB-23          5 Wednesday             7 
         8 21-FEB-23          6 Tuesday               7 
         9 20-FEB-23          7 Monday                7

现在只需选择您想要的日期-第7个日期向后计数只是工作日。

SELECT  REF_DATE "TARGET_DATE"
FROM    days
WHERE   RN = NUM_OF_DAYS

7 days from 2023-03-01
TARGET_DATE
-----------
20-FEB-23

赞(0）回复(0）举报 2023-01-25

我来回答

如何在Oracle PLSQL中创建一条语句来减去省略周末和假日的日期

3条答案

相关问题

热门标签

最新问答