CODEWITHSUNDEEP
sqlsql-servert-sql
aphrek
aphrek

Reputation: 59

Insert a Historic Row and alter rows either side accordingly

I'm trying to deal with a table that contains historic product information. On occasion I receive a historic update that I need to insert. However I'm unssure of how to alter the dates of the rows either side accordingly. I've taken a look into to lag and lead but I'm really struggling. Would anyone be able to give me some pointers on where to start, not necessarily a solution. I do have the option to alter the table structure if that will make for an easier solution. Many thanks

Initial Data in table: 

PRODUCT         VERSION   START_DATE     END_DATE
Lamp_Fitting    2         01/01/18       11/01/18 
Lamp_Fitting    2         12/01/18       NULL
Lamp_Fitting    1         24/05/17       11/11/17
Lamp_Fitting    1         12/11/17       NULL

I then receive a row with a start date of 08 Jan, 2018, meaning I need to insert it but also adjust the end date of the previous row as well as applying an end date to the inserted row. Once the row is inserted the table should appear as follows:

PRODUCT         VERSION   START_DATE     END_DATE
Lamp_Fitting    2         01/01/18       07/01/18 
Lamp_Fitting    2         08/01/18       11/01/18 
Lamp_Fitting    2         12/01/18       NULL
Lamp_Fitting    1         24/05/17       11/11/17 
Lamp_Fitting    1         12/11/17       NULL

Upvotes: 2

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this with a trigger. Or by cleaning up afterwards. The cleanup code looks like:

with toupdate as (
      select t.*,
             lead(start_date) over (partition by product, version order by start_date) as next_start_date
      from t
     )
update toupdate
    set end_date = dateadd(day, -1, next_start_date)
    where end_date <> dateadd(day, -1, next_start_date);

Of course, you can restrict this to the specific product/version that you are changing.

If you are affecting lots of rows or doing bulk inserts, then this method is recommended (bulk inserts do not fire triggers by default).

Upvotes: 2

Related Questions