在我看来,您发布的代码无论如何都不能满足您的要求(否则,您根本不会提出问题)。因此,我建议另一种方法:
改变会话(你不必这样做;我的数据库说克罗地亚语,所以我要切换到英语;另外,设置日期格式以显示日期名称):
SQL> alter session set nls_date_language = 'english';
Session altered.
SQL> alter session set nls_date_format = 'dd.mm.yyyy,dy';
Session altered.
两个 CTE 包含
- business_days:如评论中所述,仅今年 7 月,周末除外,没有节假日)
- 付款:两行,其中一个的截止日期是工作日,另一行不是
示例数据在第 15 行结束,您可能感兴趣的查询从第 16 行开始。它的 CASE
表达式检查 due_date
是否是周末日之一;如果不是,则返回的截止日期正是它。否则,另一个 SELECT
语句将返回大于 MIN
的第一个 (due_date
) 个工作日。
SQL> with
2 business_days (datum) as
3 -- for simplicity,only all dates in this year's July,4 -- weekends excluded (as they aren't business days),no holidays
5 (select date '2021-07-01' + level - 1
6 from dual
7 where to_char(date '2021-07-01' + level - 1,'dy')
8 not in ('sat','sun')
9 connect by level <= 31
10 ),11 payments (id,due_date) as
12 (select 1,date '2021-07-14' from dual -- Wednesday,business day
13 union all
14 select 2,date '2021-07-25' from dual -- Sunday,non-business day
15 )
16 select p.id,17 p.due_date current_due_date,18 --
19 case when to_char(p.due_date,'dy') not in ('sat','sun') then
20 p.due_date
21 else (select min(b.datum)
22 from business_days b
23 where b.datum > p.due_date
24 )
25 end new_due_date
26 from payments p
27 order by id;
ID CURRENT_DUE_DAT NEW_DUE_DATE
---------- --------------- ---------------
1 14.07.2021,wed 14.07.2021,wed --> Wednesday remains "as is"
2 25.07.2021,sun 26.07.2021,mon --> Sunday switched to Monday
SQL>
本文链接:https://www.f2er.com/9393.html