Update a Record in a SCD2 Table

Question

I have a table in which the entries are historized with SCD 2 the table look like this:

PK              ValidFrom   ValidTo
635582110901    04.01.2016  21.01.2016
635582110901    22.01.2016  26.01.2016
635582110901    27.01.2016  14.02.2016
635582110901    15.02.2016  10.11.2016
635582110901    11.11.2016  23.01.2017 <--
635582110901    16.11.2016  12.12.2016
635582110901    13.12.2016  18.01.2017
635582110901    19.01.2017  22.01.2017
635582110901    23.01.2017  23.01.2017
635582110901    24.01.2017  21.02.2017
635582110901    22.02.2017  31.12.9999

The record marked with the arrow is incorect This record is to be corrected with an update. so after the Update the Record look like this: (the ValidTo = ValidFrom -1 from the next Record)

635582110901    15.02.2016  10.11.2016
635582110901    11.11.2016  15.11.2016 
635582110901    16.11.2016  12.12.2016

If there are several incorect records these must also be corrected with an update ValidFrom is correct and does not have to be adjusted

Can someone please help me? Thx

Answer 1

Because your data is "almost in order", you can use lead() for this purpose:

with toupdate as (
      select t.*,
             lead(validfrom) over (partition by pk order by validfrom) as next_validfrom
      from t
     )
update toupdate
    set validto = dateadd(day, -1, next_validfrom)
    where validto <> dateadd(day, -1, next_validfrom);

I have to emphasize that this will tile all the data for a primary key. If gaps are allowed, then use this version:

update toupdate
    set validto = dateadd(day, -1, next_validfrom)
    where validto > dateadd(day, -1, next_validfrom);

This does the update only when there is an overlap.