I am looking for some advice on what type of update would be the best in a certain scenario for t-sql DML.
came into a data issue where end dates are one day before the start date on a current record, and need to set the end date to the start date of the next in row for multiple entities
rowid entity id record_type start_date end_date 214 250 1 H 2015-01-01 2014-12-31 329 250 1 H 2015-04-25 2015-04-24 533 250 1 C 2015-11-01 NULL 11 250 2 H 2015-06-01 2014-05-29 292 250 2 H 2015-09-11 2015-09-10 987 250 2 C 2015-10-01 NULL
What I need to do is update the first record end_date to the next record startdate - 1 on each employee/entity.
Currently, there are over 5K entities affected by this, so I am trying to get this updated on each record somehow to save time on this.
What I can do but is taking a lot of time, is to 1. get a max rowcount of history records for all companies into one number 2. create the same amount of temp tables for the number of total rows 3. insert minimum start date values into first temp table 4. insert minimum value not in temp table 1 into table 2, and so on 5. then update temp table 1's end date to temptable 2's startdate -1 day 6. from there, run an update on the actual table with multiple update statements for each temp table, joined on rowid.
Final output would look like:
rowid entity id record_type start_date end_date 214 250 1 H 2015-01-01 2014-04-24 329 250 1 H 2015-04-25 2015-10-31 533 250 1 C 2015-11-01 NULL 11 250 2 H 2015-06-01 2014-09-10 292 250 2 H 2015-09-11 2015-9-31 987 250 2 C 2015-10-01 NULL
Any suggestions besides my long list of temp tables/updates would be greatly appreciated! I was thinking something along the lines of possibly a cursor, but I am not too sure if this would be a quicker way of writing an update for this scenario.