By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE ps_addresses (Emplid, Effdt, State) AS
SELECT 1, DATE '2022-01-01', 'FL' FROM DUAL UNION ALL
SELECT 1, DATE '2022-01-05', 'FL' FROM DUAL UNION ALL
SELECT 1, DATE '2023-01-01', 'OH' FROM DUAL UNION ALL
SELECT 2, DATE '2021-01-01', 'NY' FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-01', 'CA' FROM DUAL UNION ALL
SELECT 3, DATE '2019-01-01', 'FL' FROM DUAL UNION ALL
SELECT 3, DATE '2023-01-01', 'TX' FROM DUAL;
7 rows affected
SELECT *
FROM ps_addresses
MATCH_RECOGNIZE(
PARTITION BY emplid
ORDER BY effdt
ALL ROWS PER MATCH
PATTERN (fl not_fl)
DEFINE
fl AS state = 'FL',
not_fl AS state != 'FL'
);
| EMPLID | EFFDT | STATE |
|---|---|---|
| 1 | 2022-01-05 00:00:00 | FL |
| 1 | 2023-01-01 00:00:00 | OH |
| 3 | 2019-01-01 00:00:00 | FL |
| 3 | 2023-01-01 00:00:00 | TX |
SELECT emplid,
effdt,
state
FROM (
SELECT p.*,
LAG(state) OVER (PARTITION BY emplid ORDER BY effdt) AS prev_state,
LEAD(state) OVER (PARTITION BY emplid ORDER BY effdt) AS next_state
FROM ps_addresses p
)
WHERE ( state = 'FL' AND next_state != 'FL' )
OR ( state != 'FL' AND prev_state = 'FL' );
| EMPLID | EFFDT | STATE |
|---|---|---|
| 1 | 2022-01-05 00:00:00 | FL |
| 1 | 2023-01-01 00:00:00 | OH |
| 3 | 2019-01-01 00:00:00 | FL |
| 3 | 2023-01-01 00:00:00 | TX |