CODEWITHSUNDEEP
sqldatabaseoracle-database
user13617491
user13617491

Reputation: 85

Oracle SQL Check current rows with previous rows dynamically

I am trying to work on something in Oracle SQL in the attached table. enter image description here

The goal is to define a Visit Type where a Primary Visit refers to any visits that happened after 3 months from previous visit(s). However, the challenge is that sometimes I'll need to compare to previous row and sometimes I need to compare with previous N rows.

For e.g., Transaction ID 3 is a revisit because its start date is within the 'end date +90 days' of Transaction ID 1 (Dec 16). Transaction ID 4 is primary because it happened after it took place after the the very first 'end date+90 days', meaning I not only need to compare to previous 1 row but previous 3 rows.

Hope this is clear!

Thanks.

See Details above. Thank you!

Upvotes: 1

Views: 337

Answers (1)

MT0
MT0

Reputation: 167867

From Oracle 12 you can use MATCH_RECOGNIZE for row-by-row pattern matching:

SELECT transaction_id, start_date, end_date, visit_type
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY start_date
  MEASURES
    CLASSIFIER() AS visit_type
  ALL ROWS PER MATCH
  PATTERN (PRIMARY revisit*)
  DEFINE revisit AS start_date <= FIRST(end_date) + INTERVAL '90' DAY 
);

Which, for the sample data:

CREATE TABLE table_name (transaction_id, start_date, end_date) AS
SELECT 1, DATE '2020-08-05', DATE '2020-09-07' FROM DUAL UNION ALL
SELECT 2, DATE '2020-09-19', DATE '2020-10-27' FROM DUAL UNION ALL
SELECT 3, DATE '2020-11-01', DATE '2020-12-19' FROM DUAL UNION ALL
SELECT 4, DATE '2021-01-23', DATE '2021-01-26' FROM DUAL UNION ALL
SELECT 5, DATE '2021-02-27', DATE '2021-03-27' FROM DUAL;

Outputs:

TRANSACTION_ID START_DATE END_DATE VISIT_TYPE
1 2020-08-05 00:00:00 2020-09-07 00:00:00 PRIMARY
2 2020-09-19 00:00:00 2020-10-27 00:00:00 REVISIT
3 2020-11-01 00:00:00 2020-12-19 00:00:00 REVISIT
4 2021-01-23 00:00:00 2021-01-26 00:00:00 PRIMARY
5 2021-02-27 00:00:00 2021-03-27 00:00:00 REVISIT

Upvotes: 2

Related Questions