Re-framing the question and the answer I have tried. PLS do not mark as duplicate, the earlier question had an answre(it did not work) hence i did not delete it I have a table ANC_PER_ABS_ENTRIES that has the following detail -

This table is a history table and the action like - insert update and delete are tracked in this table. I want to create a query that picks up only the changes in this table by comparing the last_update_date.

Eg- for person_number 101 with per_ab_entry_id -- > 15 , the action_type is insert first, then it is updated and the end_date is changed.

so in the first run, 1st row is picked, in the second run, only the update action_type should be picked.

In case of delete, i.e. for person_number 103, the inserted record that was picked in the first run was deleted. so it should be picked in the next run.

How can I use a lag function for this to check the previous run last update date and check if there is any change in start_date and end_Date, if there is an insert/update/delete, I should return the latest row. In case of delete, I should send the deleted row and in case there is a new row created for the same per_ab_entry_id then I should return that as well.

The output should look like this in the latest run-

I have tried the below query but it is not giving any output-

with anc as 
(
select person_number,
absence_type,
ABSENCE_STATUS,
approval_status_cd,
start_date,
end_date,
duration,
PER_AB_ENTRY_ID,
AUDIT_ACTION_TYPE_,
         row_number() over (order by PER_AB_ENTRY_ID, LAST_UPD_DT) rn

from ANC_PER_ABS_ENTRIES
)

select N1.person_number,
N1.absence_type,
N1.ABSENCE_STATUS,
N1.approval_status_cd,
N1.start_date,
N1.end_date,
N1.duration,
N1.PER_AB_ENTRY_ID,
N1.action_type 
from anc N1,
ANC N2
WHERE 
N1.PER_AB_ENTRY_ID = N2.PER_AB_ENTRY_ID
   and n1.rn   1 = n2.rn

AND ( n1.start_date <> n2.start_date
    or n1.end_date <> n2.end_date
    or n1.DURATION <> n2.DURATION)

MATCH_RECOGNISE WILL NOT WORK because of db

CodePudding user response：

select   PER_AB_ENTRY_ID, person_number, action_type, duration, START_dATE, END_DATE, LAST_UPD_DT
from
(
select   t.*
        ,rank() over (partition by PER_AB_ENTRY_ID order by LAST_UPD_DT desc) rnk

from     t
) t
where    rnk = 1
   or    rnk = 2 and ACTION_TYPE = 'delete'
order by PER_AB_ENTRY_ID, LAST_UPD_DT

Fiddle