Correct history data

Question

I have a scenario where i have to correct the history data. The current data is like below:

Status_cd   event_id    phase_cd    start_dt    end_dt
110         23456        30         1/1/2017    ?
110         23456        31         1/2/2017    ?


Status_cd   event_id    phase_cd    start_dt    end_dt
110           23456      30          1/1/2017   ?
111           23456      30          1/2/2017   ?

The major columns are status_cd and phase_cd. So, if any one of them change the history should be handled with the start dt of the next record as the end date of the previous record.

Here both the records are open which is not correct. Please suggest on how to handle both the scenarios.

Thanks.

Take a look at this answer, it may help you: https://stackoverflow.com/questions/8124756/teradata-equivalent-for-lead-and-lag-function-of-oracle#8130515 — Rob Paller, Jan 16 '18 at 14:43

ravioli · Accepted Answer · 2018-01-16T20:46:03.770

How are your history rows ordered in the table? In other words, how do you decide which history rows to compare to see if a value was changed? And how do you uniquely identify a history row entry?

If you order your history rows by start_dt, for example, you can compare the previous and current row values using window functions, like Rob suggested:

UPDATE MyHistoryTable
FROM (
    -- Get source history rows that need to be updated
    SELECT 
        history_row_id, -- Change this field to match your table
        MAX(status_cd) OVER(ORDER BY start_dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS status_cd_next, -- Get "status_cd" value for "next" history row
        MAX(phase_cd) OVER(ORDER BY start_dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS phase_cd_next,
        MAX(start_dt) OVER(ORDER BY start_dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS start_dt_next
    FROM MyHistoryTable
    WHERE status_cd <> status_cd_next -- Check "status_cd" values are different
    OR phase_cd <> phase_cd_next -- Check "phase_cd" values are different
) src
SET MyHistoryTable.end_dt = src.start_dt_next -- Update "end_dt" value of current history row to be "start_dt" value of next history row
WHERE MyHistoryTable.history_row_id = src.history_row_id -- Match source rows to target rows

This assumes you have a column to uniquely identify each history row, called "history_row_id". Give it a try and let me know.

I don't have a TD system to test on, so you may need to futz with the table aliases too. You'll also probably need to handle the edge cases (i.e. first/last rows in the table).

Thanks a Lot Ravioli. I will check and let u know. – user3901666 Jan 17 '18 at 19:24 — user3901666, Jan 17 '18 at 19:24

Correct history data

1 Answers1