CODEWITHSUNDEEP
sqloracle-database
Mayot
Mayot

Reputation: 41

Query : Get the previous business day of a day with is_working_day tag

We use a materialized view with all days from 01/01/2010 to 31/12/2030. There is a date field associated with a flag is_working_day 0/1.

I need to get the previous business day of a day.

Example : we are the 01/01/n, previous business day is 31/12/n-1. We are 02/01/n, previous business day is also 31/12/n-1.

I have some test code here : https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=215d5e6aa6673f2273f3766f58093bc6

sample table : 

DATE_D  DAY_OF_WEEK_DESC_EN     IS_WORKING_DAY
01-JAN-10   Friday      0
02-JAN-10   Saturday    0
03-JAN-10   Sunday      0
04-JAN-10   Monday      1
05-JAN-10   Tuesday     1
06-JAN-10   Wednesday   1
07-JAN-10   Thursday    1
08-JAN-10   Friday      1
09-JAN-10   Saturday    0
10-JAN-10   Sunday      0

select 
date_d, 
lag(date_d) over (order by date_d) as last_working_day, 
day_of_week_desc_en
from oracle_calendar
where is_working_day = 1

Expected :

DATE_D  LAST_WORKING_DAY    DAY_OF_WEEK_DESC_EN     IS_WORKING_DAY
01-JAN-10               Friday      0
02-JAN-10               Saturday    0
03-JAN-10   04-JAN-10   Sunday      0
04-JAN-10   04-JAN-10   Monday      1
05-JAN-10   04-JAN-10   Tuesday     1
06-JAN-10   05-JAN-10   Wednesday   1
07-JAN-10   06-JAN-10   Thursday    1
08-JAN-10   07-JAN-10   Friday      1
09-JAN-10   08-JAN-10   Saturday    0
10-JAN-10   08-JAN-10   Sunday      0

Basicly I use the function LAG() of Oracle to get the previous business day of a business day only, but I have to use the LEAD() function to get the last business day of a closed day. If there are consecutive closed days, I have to use this LEAD() function as many as there is consecutive closed days.

I think there is a better method to get all the previous business day of all consecutive closed days.

Upvotes: 0

Views: 137

Answers (1)

MT0
MT0

Reputation: 168051

You can use LAG with the IGNORE NULLS clause and a CASE statement:

Test Data:

CREATE TABLE oracle_calendar (
  DATE_D              DATE,
  DAY_OF_WEEK_DESC_EN VARCHAR2(9) GENERATED ALWAYS AS ( CAST( RTRIM( TO_CHAR( DATE_D, 'Day' ) ) AS VARCHAR2(9) ) ),
  IS_WORKING_DAY      NUMBER(1,0)
);

INSERT INTO oracle_calendar( date_d, is_working_day )
SELECT DATE '2010-01-01', 0 FROM DUAL UNION ALL
SELECT DATE '2010-01-02', 0 FROM DUAL UNION ALL
SELECT DATE '2010-01-03', 0 FROM DUAL UNION ALL
SELECT DATE '2010-01-04', 1 FROM DUAL UNION ALL
SELECT DATE '2010-01-05', 1 FROM DUAL UNION ALL
SELECT DATE '2010-01-06', 1 FROM DUAL UNION ALL
SELECT DATE '2010-01-07', 1 FROM DUAL UNION ALL
SELECT DATE '2010-01-08', 1 FROM DUAL UNION ALL
SELECT DATE '2010-01-09', 0 FROM DUAL UNION ALL
SELECT DATE '2010-01-10', 0 FROM DUAL;

Query:

SELECT date_d, 
       LAG( CASE is_working_day WHEN 1 THEN date_d END, 1, NULL )
         IGNORE NULLS OVER ( ORDER BY date_d) AS last_working_day, 
       day_of_week_desc_en,
       is_working_day
FROM   oracle_calendar

Output:

DATE_D    | LAST_WORKING_DAY | DAY_OF_WEEK_DESC_EN | IS_WORKING_DAY
:-------- | :--------------- | :------------------ | -------------:
01-JAN-10 | null             | Friday              |              0
02-JAN-10 | null             | Saturday            |              0
03-JAN-10 | null             | Sunday              |              0
04-JAN-10 | null             | Monday              |              1
05-JAN-10 | 04-JAN-10        | Tuesday             |              1
06-JAN-10 | 05-JAN-10        | Wednesday           |              1
07-JAN-10 | 06-JAN-10        | Thursday            |              1
08-JAN-10 | 07-JAN-10        | Friday              |              1
09-JAN-10 | 08-JAN-10        | Saturday            |              0
10-JAN-10 | 08-JAN-10        | Sunday              |              0

db<>fiddle here

Query 2:

To get rid of all the NULL values at the start and use the first working day:

SELECT date_d, 
       COALESCE(
         LAG( CASE is_working_day WHEN 1 THEN date_d END, 1, NULL )
           IGNORE NULLS OVER ( ORDER BY date_d),
         CASE is_working_day
         WHEN 1
         THEN date_d
         ELSE LEAD( CASE is_working_day WHEN 1 THEN date_d END, 1, NULL )
           IGNORE NULLS OVER ( ORDER BY date_d)
         END
       ) AS last_working_day,
       day_of_week_desc_en,
       is_working_day
FROM   oracle_calendar

DATE_D    | LAST_WORKING_DAY | DAY_OF_WEEK_DESC_EN | IS_WORKING_DAY
:-------- | :--------------- | :------------------ | -------------:
01-JAN-10 | 04-JAN-10        | Friday              |              0
02-JAN-10 | 04-JAN-10        | Saturday            |              0
03-JAN-10 | 04-JAN-10        | Sunday              |              0
04-JAN-10 | 04-JAN-10        | Monday              |              1
05-JAN-10 | 04-JAN-10        | Tuesday             |              1
06-JAN-10 | 05-JAN-10        | Wednesday           |              1
07-JAN-10 | 06-JAN-10        | Thursday            |              1
08-JAN-10 | 07-JAN-10        | Friday              |              1
09-JAN-10 | 08-JAN-10        | Saturday            |              0
10-JAN-10 | 08-JAN-10        | Sunday              |              0

db<>fiddle here

Upvotes: 2

Related Questions