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.

1. Insert means when the entry in the table was added
2. Update means some sort of changes were made in the table
3. Delete means the record was deleted.

I want to create a query that picks up the changes in this table by comparing the last_update_date and the run_date (parameter)

Eg- for person_number 101 with per_ab_entry_id -- > 15 , the action_type is insert first that means the record was created on first, then it is updated and the end_date , duration is changed.

so if i run the below query on the 1st after 4:59, then the 1st row will be picked. When I run it on 2nd , only the 2nd row is getting picked.

But how i want is that in case sthe same per_ab_entry_id was updated and if the last_upd_dt of the update >= run_Date then , the insert row should also be extracted -

The output should look like this in the latest run-

I have to run the below query such that the last_update_date >= :process_date. Its working for the delete condition and evrything except this case. How can it be tweaked that when the last_upd_dt of the latest recorrd of one per_ab_entry_id >=process_date then its previous row is also sent.

The below query is not working because the last_upd_dt of the 1st row <= process_date

    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 * FROM ANC 
where    RN = 1
   or    RN = 2 and UPPER(flag) = 'D'
   and APPROVAL_STATUS_CD = 'Approved'
   and last_update_date >=:process_date
   ORder by PER_AB_ENTRY_ID, LAST_UPD_DT

CodePudding user response：

I understand that you want to find entries that were updated recently, and display their whole change log (starting with the initial insert).

Here is one way to do it with window functions:

select a.*
from (
    select a.*, 
        max(last_update_date) over(partition by per_ab_entry_id) max_update_date
    from anc_per_abs_entries a
) a
where max_update_date >= :process_date
order by per_ab_entry_id, last_update_date

In the subquery, the window max computes the latest update amongst all rows that belong to the same entry ; we can then use that information to filter in the outer query.

I was not sure about the filtering logic at the end of your SQL code, which is not described in the text of the question, so I left it apart - you might need to reincorporate it.

CodePudding user response：

Try to create CTE from your table like this:

WITH
    anc AS
        (
            Select 
                ROW_NUMBER() OVER(Partition By PER_AB_ENTRY_ID, PERSON_NUMBER Order By PER_AB_ENTRY_ID, PERSON_NUMBER, LAST_UPDATE_DATE) "RN", 
                Count(*) OVER(Partition By PER_AB_ENTRY_ID, PERSON_NUMBER Order By PER_AB_ENTRY_ID, PERSON_NUMBER) "MAX_RN",
                a.*, Max(LAST_UPDATE_DATE) OVER(Partition By PER_AB_ENTRY_ID, PERSON_NUMBER Order By PER_AB_ENTRY_ID, PERSON_NUMBER, LAST_UPDATE_DATE) "MAX_UPD_DATE"
            From anc_per_abs_entries a
        )

Now you have last (max) update date along with row numbers and total number of rows per person and entry id.
The main SQL should do the job:

SELECT
    a.RN,
    a.MAX_RN,
    a.PER_AB_ENTRY_ID,
    a.PERSON_NUMBER,
    a.ACTION_TYPE,
    a.DURATION,
    a.START_DATE,
    a.END_DATE,
    a.LAST_UPDATE_DATE,
    To_Char(a.MAX_UPD_DATE, 'dd.mm.yyyy hh24:mi:ss') "MAX_UPD_DATE"
FROM 
    anc a
WHERE
    LAST_UPDATE_DATE <= :process_date
  AND
  (
    (
        TRUNC(a.MAX_UPD_DATE) = TRUNC(:process_date) And
        a.MAX_UPD_DATE <= :process_date And
        a.RN = a.MAX_RN
    )
  OR
    (
        TRUNC(a.MAX_UPD_DATE) = TRUNC(:process_date) And
        a.MAX_UPD_DATE > :process_date And
        a.RN = a.MAX_RN - 1
    )
  OR
    (
        TRUNC(a.MAX_UPD_DATE) != TRUNC(:process_date) And
        a.MAX_UPD_DATE > :process_date And
        a.RN IN(a.MAX_RN, a.MAX_RN - 1)
    )
  )

Now you can adjust every part of the OR conditions to suit your needs. For instance I'm not sure what you want to select if second group of conditions is satisfied. It is either that in the code or maybe like here:

...
  OR
    (
        TRUNC(a.MAX_UPD_DATE) = TRUNC(:process_date) And
        a.MAX_UPD_DATE > :process_date And
        a.RN = CASE WHEN a.MAX_RN > 1 THEN a.MAX_RN - 1 ELSE a.MAX_RN END
    )
...

... or whatever suits you.
Regards...