Extract date range from a date column via Flag column

This is table structure enter image description here

Output result enter image description here

Want to get above output. Extract date range from a date column where Flag ‘N’ is available till ‘Y’ flag take between date from_date to to_date Second case: extract date range from a date column where Flag ‘Y’ started and till ‘Y’ and between ‘N’ flags are available then take between date from_date to to_date Third case: Extract date range from a date column where Flag ‘Y’ is started to till ‘N’ flag take between date from_date to to_date

Output result enter image description here The above output want to extract

Extract date range from a date column via Flag column

This is table structure enter image description here

Output result enter image description here

Want to get above output. Extract date range from a date column where Flag ‘N’ is available till ‘Y’ flag take between date from_date to to_date Second case: extract date range from a date column where Flag ‘Y’ started and till ‘Y’ and between ‘N’ flags are available then take between date from_date to to_date Third case: Extract date range from a date column where Flag ‘Y’ is started to till ‘N’ flag take between date from_date to to_date

Output result enter image description here The above output want to extract

• oracle-database

• 1 Please do no post screenshots as mentioned in How to ask a good question. – Koen Lostrie Commented Mar 28 at 13:49

1 Answer 1

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

SELECT *
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY cust_id, account_no
         ORDER BY eod_date
         MEASURES
           FIRST(eod_date) AS from_eod_date,
           LAST(eod_date) AS to_eod_date
         PATTERN (Y+)
         DEFINE Y AS flag = 'Y'
       )

Which, for the sample data:

CREATE TABLE table_name (cust_id, account_no, eod_date, flag) AS
SELECT 'C_01',
       'ACCT01',
       DATE '2025-02-01' + LEVEL - 1,
       CASE WHEN LEVEL IN (3, 6, 7, 8, 10, 11, 12, 15) THEN 'Y' ELSE 'N' END
FROM   DUAL
CONNECT BY LEVEL <= 20;

Outputs:

fiddle