Reputation: 81
IF / CASE statement in Where in Oracle (OR)
I want to search for a rows between two dates. In each row there is a column with date. I want to decrease this date by 1 and always display the results with that column decreased by 1 day.
For example - I'm searching between 2021-07-08 00:00:00 and 2021-07-08 23:59:59 so I want to search for columns with date 2021-07-09 but display them as 2021-07-08.
The problem is that I want to exclude from that searching holidays and weekend. So for example if I will search between 2021-07-09 00:00:00 and 2021-07-09 23:59:59 then I want to search for columns with with date 2021-07-12 and display them as 2021-07-09.
For holidays I have a list:
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')))
)
How to check in where clause that we have :start or :end date into that list or weekend.
I've tried with:
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
)
But it seems like executing of the query goes forever...
l.oracletimezone is an column with timezone for different locations.
Of course I'm using decreasing also in select.
Without OR statements it works but as I said only between monday and thursday. If we select between friday date then we will get nothing cause there is no 'DUEDATE' until weekend days.
Is my logic is wrong here?
Example:
| id | name | duedate |
|---|---|---|
| 1 | Electricity bill | 2021-07-08 |
| 2 | Water bill | 2021-07-09 |
| 3 | Rent bill | 2021-07-12 |
Search between 2021-07-07 00:00:00 and 2021-07-07 23:59:59
Result:
| id | name | duedate |
|---|---|---|
| 1 | Electricity bill | 2021-07-07 |
Search between 2021-07-08 00:00:00 and 2021-07-08 23:59:59
Result:
| id | name | duedate |
|---|---|---|
| 1 | Water bill | 2021-07-08 |
Search between 2021-07-09 00:00:00 and 2021-07-09 23:59:59
Result:
| id | name | duedate |
|---|---|---|
| 1 | Rent bill | 2021-07-09 |
Search between 2021-07-07 00:00:00 and 2021-07-09 23:59:59
Result:
| id | name | duedate |
|---|---|---|
| 1 | Electricity bill | 2021-07-07 |
| 2 | Water bill | 2021-07-08 |
| 3 | Rent bill | 2021-07-09 |
Upvotes: 0
Views: 82
Answers (1)
Reputation: 168212
You can create the function:
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;
/
Then, if you have the sample data:
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;
Then:
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 )
Gets the bill due on the next day and outputs:
ID NAME DUEDATEUTC 1 Electricity bill 2021-08-20 00:00:00
and:
SELECT *
FROM your_table o
WHERE o.duedateutc BETWEEN next_working_day( DATE '2021-08-20' + 1 )
AND next_working_day( DATE '2021-08-20' + INTERVAL '23:59:59' HOUR TO SECOND + 1 )
Skips the weekend and gets the bill on the next Monday and outputs:
ID NAME DUEDATEUTC 2 Water bill 2021-08-23 00:00:00
and:
SELECT *
FROM your_table o
WHERE o.duedateutc BETWEEN next_working_day( DATE '2021-08-27' + 1 )
AND next_working_day( DATE '2021-08-27' + INTERVAL '23:59:59' HOUR TO SECOND + 1 )
Skips the weekend and the Monday holiday and gets the bill on the next Tuesday and outputs:
ID NAME DUEDATEUTC 3 Rent bill 2021-08-31 00:00:00
and:
SELECT *
FROM your_table o
WHERE o.duedateutc BETWEEN next_working_day( DATE '2021-08-19' + 1 )
AND next_working_day( DATE '2021-08-27' + INTERVAL '23:59:59' HOUR TO SECOND + 1 )
Gets all the previous bills, outputting:
ID NAME DUEDATEUTC 1 Electricity bill 2021-08-20 00:00:00 2 Water bill 2021-08-23 00:00:00 3 Rent bill 2021-08-31 00:00:00
and:
SELECT *
FROM your_table o
WHERE o.duedateutc BETWEEN next_working_day( DATE '2021-12-24' + 1 )
AND next_working_day( DATE '2021-12-24' + INTERVAL '23:59:59' HOUR TO SECOND + 1 )
Skips the weekend and the 2-day Christmas holiday and gets the bill on the next Wednesday, outputting:
ID NAME DUEDATEUTC 4 XYZ bill 2021-12-29 00:00:00
db<>fiddle here
Upvotes: 2
Related Questions
- Conditional WHERE clause with CASE statement in Oracle
- Oracle (SQL Statements) - Using CASE with WHERE CLAUSE
- Conditional Where?
- Case in Oracle WHERE clause
- Oracle query case statement in the where clause
- oracle where clause with case when
- Using Case When Clause in Where Clause
- Oracle with CASE Statement in WHERE clause
- Oracle SQL- Writing case if inside the where clause
- Case statement in where