Trying to find the last time that a value has changed from Current Table and History Table

Question

We have a few variable fields which maintained in two table.

Table 1: This maintains the current snapshot of the fields

Table 2: Any changes on the variable fields it moves all the rows from Table 1 to Table 2 then inserts all the rows again back to Table 1 with the changed value.

Example:

Snapshot 1

Table 1:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS
Group1     F1         V1          T1
Group1     F2         V2          T1
Group1     F3         V3          T1
Group2     F1         VX          TX

Table 2:

NO ROWS

Snapshot 2 --> Group1 F3 value has chnaged to V33

Table 1:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS
Group1     F1         V1          T2
Group1     F2         V2          T2
Group1     F3         V33         T2
Group2     F1         VX          TX

Table 2:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS HIST_TS
Group1     F1         V1          T1          HT1
Group1     F2         V2          T1          HT2
Group1     F3         V3          T1          HT3

Snapshot 3 --> Group1 F2 value has changed to V22

Table 1:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS
Group1     F1         V1          T3
Group1     F2         V22         T3
Group1     F3         V33         T3
Group2     F1         VX          TX

Table 2:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS HIST_TS
Group1     F1         V1          T1          HT1
Group1     F2         V2          T1          HT2
Group1     F3         V3          T1          HT3
Group1     F1         V1          T2          HT4
Group1     F2         V2          T2          HT5
Group1     F3         V33         T2          HT6

Snapshot 4 --> Group1 F3 value has changed to V333

Table 1:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS
Group1     F1         V1          T4
Group1     F2         V22         T4
Group1     F3         V333        T4
Group2     F1         VX          TX

Table 2:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS HIST_TS
Group1     F1         V1         T1          HT1
Group1     F2         V2         T1          HT2
Group1     F3         V3         T1          HT3
Group1     F1         V1         T2          HT4
Group1     F2         V2         T2          HT5
Group1     F3         V33        T2          HT6
Group1     F1         V1         T3          HT7
Group1     F2         V22        T3          HT8
Group1     F3         V33        T3          HT9

HT stands for history time means when the fields moved from Table 1 to Table 2

I want to fetch in last 2 hours which fields has been changed with actual timestamp of change from Table 1 and Table 2

Expected Result:

VAR_GRP    VAR_FLD    VAR_FLD_VAL LST_UPDT_TS
Group1     F2         V22         T3
Group1     F3         V333        T4

Answer 1

Here is an answer which selects the records in your expected result set from test data based on the sample you provide. I phrase it in such a weasly fashion simply because your question doesn't fully express the required business rules, so this is a bit of a guess:

select t1.VAR_GRP,  
       t1.VAR_FLD , 
       t1.VAR_FLD_VAL,
       min(case when t1.VAR_FLD_VAL != t2.VAR_FLD_VAL then t1.LST_UPDT_TS
            else t2.min_LST_UPDT_TS end) as LST_UPDT_TS
from table1 t1
     join ( select VAR_GRP,  
                   VAR_FLD , 
                   VAR_FLD_VAL,
                   min(LST_UPDT_TS) over (partition by VAR_GRP, VAR_FLD, VAR_FLD_VAL) min_LST_UPDT_TS,
                   row_number() over (partition by VAR_GRP, VAR_FLD, VAR_FLD_VAL order by HIST_TS desc) rn
            from  table2 
            ) t2 on t1.VAR_GRP = t2.VAR_GRP
                and t1.VAR_FLD = t2.VAR_FLD
where t2.rn = 1
and (t2.min_LST_UPDT_TS >= to_date('2018-06-10 11:00:00', 'yyyy-mm-dd hh24:mi:ss') - (1/24) or  t1.VAR_FLD_VAL != t2.VAR_FLD_VAL) 
group by t1.VAR_GRP,  
       t1.VAR_FLD , 
       t1.VAR_FLD_VAL
/

So the business rules I implemented were:

• calculate the minimum for LST_UPDT_TS for records in table2
• if the minimum for LST_UPDT_TS is within last two hours
• or the value in table1 does not match the value in table2
• then include in the result set

Here is a LiveSQL demo (free Oracle account required - sorry but SQL Fiddle has been rather flaky of late).

Please note that this solution does not work if the changes are INSERT a record for a new (VAR_GRP,VAR_FLD) into table1 or DELETE a record from table1. I'm not sure how you will represent those activities in table2.

---

For the benefit of other Seekers, in a comment I described the journalling implementation as "bonkers". Why so harsh? Because the process generates a lot of unnecessary work and loses crucial information into the bargain:

1. The journalling process takes a snapshot of all the existing records in the VAR_GRP which owns the record which has changed. It would be less work to populate table2 with just the old version of the changed record.
2. The process updates (or, seemingly, deletes and re-inserts) all the LST_UPDT_TS values in table1 of all the existing records in the VAR_GRP which owns the record which has changed. It would be less work to just update the timestamp of the changed record.
3. Because the process takes a snapshot of all the table1 records it is impossible to see which record was changed in table2 without doing a lot of work.
4. Because the process updates all the records in table1 it is impossible to see when each record actually changed without consulting table2.

Journalling (AKA auditing, history) is an overhead, because it fires whenever we execute DML on our tables. So it is considered good practice to minimize the impact on the database; this implementation does the exact opposite. It also throws away the single most important piece of information - which record was changed - and demands we infer it from the other records. Poor show.

A better implementation would be to insert the old version of just the changed table1 record into table2 and mark the changed table1 record with the timestamp.