WITH test_pattern AS (
SELECT 100 id, 'AAA' ident, DATE '2020-01-01' dat_from, 'N' cancel,'ccc' attr1,'fff' attr2 FROM dual UNION ALL
SELECT 101 id, 'AAA' ident, DATE '2021-01-01' dat_from, 'N' cancel,'ccc' attr1,'fff' attr2 FROM dual UNION ALL
SELECT 102 id, 'AAA' ident, DATE '2021-01-01' dat_from, 'J' cancel,'ccc' attr1,'fff' attr2 FROM dual UNION ALL
SELECT 103 id, 'AAA' ident, DATE '2021-01-01' dat_from, 'N' cancel,'ccc' attr1,'fff' attr2 FROM dual UNION ALL
SELECT 110 id, 'BBB' ident, DATE '2019-01-01' dat_from, 'N' cancel,'ccc' attr1,'fff' attr2 FROM dual UNION ALL
SELECT 111 id, 'BBB' ident, DATE '2020-01-01' dat_from, 'N' cancel,'ddd' attr1,'kkk' attr2 FROM dual UNION ALL
SELECT 112 id, 'BBB' ident, DATE '2021-01-01' dat_from, 'N' cancel,'ddd' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 113 id, 'BBB' ident, DATE '2021-01-01' dat_from, 'J' cancel,'ddd' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 114 id, 'BBB' ident, DATE '2020-01-01' dat_from, 'J' cancel,'ddd' attr1,'kkk' attr2 FROM dual UNION ALL
SELECT 115 id, 'BBB' ident, DATE '2020-01-01' dat_from, 'N' cancel,'ddd' attr1,'kkk' attr2 FROM dual UNION ALL
SELECT 116 id, 'BBB' ident, DATE '2021-01-01' dat_from, 'N' cancel,'ddd' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 117 id, 'BBB' ident, DATE '2022-01-01' dat_from, 'N' cancel,'lll' attr1,'iii' attr2 FROM dual UNION ALL
SELECT 120 id, 'CCC' ident, DATE '2019-01-01' dat_from, 'N' cancel,'bbb' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 121 id, 'CCC' ident, DATE '2020-01-01' dat_from, 'N' cancel,'ddd' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 122 id, 'CCC' ident, DATE '2021-01-01' dat_from, 'N' cancel,'hhh' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 123 id, 'CCC' ident, DATE '2021-01-01' dat_from, 'J' cancel,'hhh' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 124 id, 'CCC' ident, DATE '2020-01-01' dat_from, 'J' cancel,'ddd' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 125 id, 'CCC' ident, DATE '2020-01-01' dat_from, 'N' cancel,'eee' attr1,'ggg' attr2 FROM dual UNION ALL
SELECT 126 id, 'CCC' ident, DATE '2021-01-01' dat_from, 'N' cancel,'hhh' attr1,'ggg' attr2 FROM dual
)
SELECT *
FROM test_pattern;
Data are incoming sorted by id.
For each ident the dat_from is ascending.
When a dataset is canceled then cancel = J and it is canceled in reverse order.
Usually new data is sent, often with the same attributes attr1/attr2. I want to identify these datasets where the new data is identical to the cancelled data so I can eliminate them befor further processing.
AAA: the data for 1.1.21 is cancelled and sent identically -> eliminate
BBB: the data for 1.1.20 and 1.1.21 is cancelled and sent identically -> eliminate
CCC: the data for 1.1.20 and 1.1.21 is cancelled but data for 1.1.20 is different -> process
Identifying a single cancel/new is easy, but how can I find cases like BBB where more than one dataset is cancelled and sent again identically?