Fixing Incorrect Data in Table using the same table

Question

Table Location (LocId, StartDt,EndDt) has incorrect EndDt. The second table is how it should look. The location table is in prod Db. I'm using MS SQL Server

Table Location
LocId   StartDt          EndDT
1        Jan-23-2015    Dec-31-9999
1        Feb-15-2015    Dec-31-9999
2        Mar-18-2015    Apr-28-2015
2        Nov-23-2015   Dec-31-9999
2        Jul-23-2015     Nov-23-2015
2        Apr-28-2015    Dec-31-9999

This is how the final table should look which is below
Location
LocId      StartDt          EndDT
1         Jan-23-2015      Feb-15-2015
1         Feb-15-2015     Dec-31-9999
2        Mar-18-2015      Apr-28-2015
2        Apr-28-2015      Jul-23-2015
2        Jul-23-2015       Nov-23-2015
2        Nov-23-2015     Dec-31-9999

How should I write a select query that will get me the results as per the second table using the first one.

What steps should I perform and what would be the update query to correct the data in location table.

Answer 1

I think you want lead():

select l.*,
       (case when enddt = '9999-12-31'
             then lead(startdt) over (partition by locid order by startdt)
             else enddt
        end) as new_enddt
from location l;

You can incorporate this into an update using a CTE:

with toupdate as (
      select l.*,
             (case when enddt = '9999-12-31'
                   then lead(startdt) over (partition by locid order by startdt)
                   else enddt
              end) as new_enddt
      from location l
     )
update toupdate
    set enddt = new_enddt
    where enddt <> new_enddt;

Answer 2

The update statement you want would look something like this:

UPDATE
    Tab1
SET
    Tab1.[StartDT] = Tab2.[StartDT],
    Tab1.[EndDT] = Tab2.[EndDT]
FROM
    [Location] Tab1
    JOIN [Location] Tab2
        ON Tab1.[LocId] = Tab2.[Loc2]

You could then use the same join for the SELECT statement.