Oracle(OR)中Where中的IF/CASE语句

Question

我想搜索两个日期之间的行。每一行都有一列日期。我希望将此日期减少1，并始终显示该列减少1天的结果。

例如，我在2021-07-08 00:00:00和2022-07-08 23:59:59之间搜索，因此我希望搜索日期为22001-07-09的列，但将其显示为<strong]2021-07-08>。

问题是，我想把假期和周末排除在搜索之外。因此，例如，如果我将在2021-07-09 00:00:00和2022-07-09 23:59:59之间搜索，则我希望搜索日期为22021-07-12的列，并将其显示为<strong]2021-07-09>。

假期我有一个清单：

with BANKHOLIDAYSUK as(
select COLUMN_VALUE as HOLIDAYDATE
from table(sys.odcivarchar2list  (
TO_DATE('30/08/2021', 'DD/MM/YYYY')
,TO_DATE('27/12/2021', 'DD/MM/YYYY')
,TO_DATE('28/12/2021', 'DD/MM/YYYY')
,TO_DATE('01/01/2022', 'DD/MM/YYYY')
,TO_DATE('03/01/2022', 'DD/MM/YYYY')
,TO_DATE('15/04/2022', 'DD/MM/YYYY')
,TO_DATE('18/04/2022', 'DD/MM/YYYY')
,TO_DATE('02/05/2022', 'DD/MM/YYYY')
,TO_DATE('02/06/2022', 'DD/MM/YYYY')
,TO_DATE('03/06/2022', 'DD/MM/YYYY')
,TO_DATE('29/08/2022', 'DD/MM/YYYY')
,TO_DATE('26/12/2022', 'DD/MM/YYYY')
,TO_DATE('27/12/2022', 'DD/MM/YYYY')
,TO_DATE('01/01/2023', 'DD/MM/YYYY')
,TO_DATE('02/01/2023', 'DD/MM/YYYY')
,TO_DATE('07/04/2023', 'DD/MM/YYYY')
,TO_DATE('10/04/2023', 'DD/MM/YYYY')
,TO_DATE('01/05/2023', 'DD/MM/YYYY')
,TO_DATE('29/05/2023', 'DD/MM/YYYY')
,TO_DATE('28/08/2023', 'DD/MM/YYYY')
,TO_DATE('25/12/2023', 'DD/MM/YYYY')
,TO_DATE('26/12/2023', 'DD/MM/YYYY')
,TO_DATE('09/07/2021', 'DD/MM/YYYY')))
)

如何将日期为：开始或:结束的where子句签入该列表或周末。

我尝试过：

where 
to_date(to_char(from_tz( cast( (o.DUEDATEUTC - 1)  as timestamp ), 'UTC' ) at time zone to_char(l.oracletimezone  ), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') between :startDate and :endDate
OR (
(SELECT * FROM BANKHOLIDAYSUK WHERE HOLIDAYDATE = TO_DATE(:startdate, 'DD/MM/YYYY')) is not null 
and to_date(to_char(from_tz( cast( (o.DUEDATEUTC - 1)  as timestamp ), 'UTC' ) at time zone to_char(l.oracletimezone  ), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') between :startDate and :endDate
)
OR
(
((SELECT to_char(:startDate, 'd') FROM DUAL) = 5)
and to_date(to_char(from_tz( cast( (o.DUEDATEUTC - 3)  as timestamp ), 'UTC' ) at time zone to_char(l.oracletimezone  ), 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') between :startDate and :endDate
)

但查询的执行似乎永远不会停止。。。

l.oracletimezone是一个具有不同位置时区的列。

当然，我在select中也使用递减。

如果没有OR声明，它是有效的，但正如我所说，只是在周一到周四之间。如果我们在周五之间选择，那么我们将一无所获，因为在周末之前没有"到期日"。

我的逻辑错了吗？

示例：

决斗日期

id	名称
1	电费	2021-07-08
2	水费	2021-07-09
3	租金账单	2021-07-12

您可以创建函数：

CREATE FUNCTION next_working_day(
day IN DATE
) RETURN DATE
IS
working_day DATE;
BEGIN
working_day := day + CASE TRUNC(day) - TRUNC(day, 'IW')
WHEN 5 THEN 2 -- Saturday
WHEN 6 THEN 1 -- Sunday
ELSE 0        -- Weekday
END;

WITH non_holiday_date ( day, skip ) AS (
SELECT working_day,
NVL2(
b.holidaydate,
CASE TRUNC(working_day) - TRUNC(working_day, 'IW')
WHEN 4 THEN 3 -- Friday
ELSE 1        -- Any other weekday
END,
0
)
FROM   DUAL d
LEFT OUTER JOIN bankholidaysuk b
ON (TRUNC(working_day) = b.holidaydate)
UNION ALL
SELECT day + skip,
NVL2(
b.holidaydate,
CASE TRUNC(day) - TRUNC(day, 'IW')
WHEN 4 THEN 3 -- Friday
ELSE 1        -- Any other weekday
END,
0
)
FROM   non_holiday_date n
LEFT OUTER JOIN bankholidaysuk b
ON (TRUNC(day) + skip = b.holidaydate)
WHERE  n.skip > 0
)
SELECT day
INTO   working_day
FROM   non_holiday_date
WHERE  skip = 0;

RETURN working_day;
END;
/

然后，如果你有样本数据：

CREATE TABLE your_table (id, name, duedateutc) AS
SELECT 1, 'Electricity bill', DATE '2021-08-20' FROM DUAL UNION ALL
SELECT 2, 'Water bill',       DATE '2021-08-23' FROM DUAL UNION ALL
SELECT 3, 'Rent bill',        DATE '2021-08-31' FROM DUAL UNION ALL
SELECT 4, 'XYZ bill',         DATE '2021-12-29' FROM DUAL;
CREATE TABLE BANKHOLIDAYSUK ( holidaydate ) as
SELECT DATE '2021-08-30' FROM DUAL UNION ALL
SELECT DATE '2021-12-27' FROM DUAL UNION ALL
SELECT DATE '2021-12-28' FROM DUAL;

然后：

SELECT *
FROM   your_table o
WHERE  o.duedateutc BETWEEN next_working_day( DATE '2021-08-19' + 1 )
AND     next_working_day( DATE '2021-08-19' + INTERVAL '23:59:59' HOUR TO SECOND + 1 )

获取第二天到期的账单并输出：

ID	名称	DUEDATEUTC
1	电费	2021-08-20 00:00:00