I want active ids but not those record which have I' as status till next record for that id but if the previous record has status 'A' then it should come but not after the status 'I record'.

INPUT:

OUTPUT:

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing. To get the rows for each id with the earliest status of A until the first status I row, you can use:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER BY start_date
  ALL ROWS PER MATCH
  PATTERN ( ^ a_status  )
  DEFINE a_status AS status = 'A'
)

Which, for the sample data:

CREATE TABLE table_name (id, start_date, end_date, status) AS
SELECT 1000000278, DATE '2021-08-25', DATE '2022-08-25', 'I' FROM DUAL UNION ALL
SELECT 1000000278, DATE '2022-08-25', DATE '2023-08-25', 'A' FROM DUAL UNION ALL
SELECT 1000000284, DATE '2021-08-20', DATE '2022-08-25', 'A' FROM DUAL UNION ALL
SELECT 1000000284, DATE '2022-08-25', DATE '2023-08-25', 'A' FROM DUAL UNION ALL
SELECT 1000000285, DATE '2024-08-20', DATE '2028-08-20', 'A' FROM DUAL UNION ALL
SELECT 1000000285, DATE '2028-08-21', DATE '2030-08-20', 'I' FROM DUAL UNION ALL
SELECT 1000000285, DATE '2030-08-21', DATE '2031-08-20', 'A' FROM DUAL UNION ALL
SELECT 1000000286, DATE '2021-08-25', DATE '2022-08-25', 'A' FROM DUAL;

Outputs:

fiddle