Fixing Incorrect Data in Table using the same table

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.

---

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;

---

I would use LEAD function (requires SQL2012+):

DECLARE @TargetTable TABLE (
    ID      INT IDENTITY(1, 1) PRIMARY KEY,
    LocId   INT  NULL,
    StartDt DATE NULL,
    EndDt   DATE NULL
)

INSERT  @TargetTable (LocId, StartDt, EndDt)
SELECT  s.LocId, TRY_PARSE(s.StartDt AS DATE), TRY_PARSE(s.EndDT AS DATE)
FROM (VALUES    
    (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')
) s(LocId, StartDt, EndDT);

WITH CteUpdate
AS (
    SELECT  t.EndDt, ISNULL(LEAD(t.StartDt) OVER(PARTITION BY t.LocId ORDER BY t.StartDt), '9999-12-31') AS NewEndDt
    FROM    @TargetTable t
) 
UPDATE  CteUpdate
SET     EndDt = NewEndDt;

SELECT * FROM @TargetTable ORDER BY LocId, StartDt

Results:

ID          LocId       StartDt    EndDt
----------- ----------- ---------- ----------
1           1           2015-01-23 2015-02-15
2           1           2015-02-15 9999-12-31
3           2           2015-03-18 2015-04-28
6           2           2015-04-28 2015-07-23
5           2           2015-07-23 2015-11-23
4           2           2015-11-23 9999-12-31

---

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.

链接地址: http://www.djcxy.com/p/65616.html

上一篇: 每月分组

下一篇: 使用同一个表修复表中的数据不正确