Set output to 1 if 5 or more days have passed since the last eligible entry-CodePudding

I have the below table:

ID  DATE_CREATION
-----------------
S1     01/01/2022
S2     04/01/2022
S3     07/01/2022
S4     09/01/2022
S5     10/01/2022

I would like to implement this logic: the oldest line (S1) will have output 1. For the subsequent lines, if 5 or more days have passed from the previous entry with an output = 1, mark that output as 1 and continue evaluating.

The expected output would look something like this (4th column included for explanation purposes).

ID  DATE_CREATION   OUTPUT  OUTPUT_EXPLANATION
----------------------------------------------
S1     01/01/2022        1  It is the first entry
S2     04/01/2022        0  5 days have not passed since the last eligible entry (S1)
S3     07/01/2022        1  5 days have passed since the last eligible entry (S1)
S4     09/01/2022        0  5 days have not passed since the last eligible entry (S3) 
S5     10/01/2022        0  5 days have not passed since the last eligible entry (S3)

CodePudding user response：

From Oracle 12, you can use:

SELECT id,
       date_creation,
       CASE RN WHEN 1 THEN 1 ELSE 0 END AS output
FROM   table_name
MATCH_RECOGNIZE (
  ORDER BY DATE_CREATION
  MEASURES
    COUNT(*) AS rn
  ALL ROWS PER MATCH
  PATTERN (five_days )
  DEFINE
    five_days AS date_creation < FIRST(date_creation)   INTERVAL '5' DAY
)

Which, for the sample data:

CREATE TABLE table_name (ID, DATE_CREATION) AS
SELECT 'S1', DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 'S2', DATE '2022-01-04' FROM DUAL UNION ALL
SELECT 'S3', DATE '2022-01-07' FROM DUAL UNION ALL
SELECT 'S4', DATE '2022-01-09' FROM DUAL UNION ALL
SELECT 'S5', DATE '2022-01-10' FROM DUAL;

Outputs:

ID DATE_CREATION OUTPUT

S1 01-JAN-22 1

S2 04-JAN-22 0

S3 07-JAN-22 1

S4 09-JAN-22 0

S5 10-JAN-22 0

ID	DATE_CREATION	OUTPUT
S1	01-JAN-22	1
S2	04-JAN-22	0
S3	07-JAN-22	1
S4	09-JAN-22	0
S5	10-JAN-22	0

db<>fiddle here