SELECT 'A', 'B', 'C', 1, 1, 201912 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 1, 1, 202001 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 1, 1, 202002 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 1, 1, 202003 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 2, 1, 202004 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 2, 1, 202005 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 1, 1, 202006 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 1, 1, 202007 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 1, 1, 202008 FROM DUAL UNION ALL

SELECT 'A', 'B', 'C', 1, 1, 202009 FROM DUAL;

code2,

code3,

rate,

value,

start_dt,

CASE end_dt

WHEN TO_NUMBER( TO_CHAR( SYSDATE, 'YYYYMM' ) )

THEN 999912

ELSE end_dt

END AS end_dt

FROM table_name

MATCH_RECOGNIZE (

PARTITION BY code1, code2, code3

ORDER BY month

MEASURES FIRST( rate ) AS rate,

FIRST( value ) AS value,

FIRST( month ) AS start_dt,

LAST( month ) AS end_dt

ONE ROW PER MATCH

PATTERN (FIRST_ROW EQUAL_ROWS*)

DEFINE EQUAL_ROWS AS (

EQUAL_ROWS.rate = PREV(EQUAL_ROWS.rate)

AND EQUAL_ROWS.value = PREV(EQUAL_ROWS.value)

AND TO_DATE( EQUAL_ROWS.month, 'YYYYMM' )

= ADD_MONTHS( TO_DATE( PREV(EQUAL_ROWS.month), 'YYYYMM' ), 1 )

)

)