AmBlack AmBlack - 3 months ago 13
SQL Question

Update a Record in a SCD2 Table

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 Source

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.