問題描述

我有以下員工版本控制表:

I have the following Employee versioning table:

EmployeeId ManagerId DepartmentId StartDate   EndDate
--------------------------------------------------------
45         2         56           2017-06-27  2018-02-07
45         3         98           2018-02-07  2018-08-25
45         3         55           2018-02-25  2018-08-25
45         6         44           2018-08-25  9999-12-31

我想更正 StartDate 如下以避免重疊:

I want to correct StartDate as below to avoid the overlap as below:

EmployeeId ManagerId DepartmentId StartDate   EndDate
---------------------------------------------------------
45         2         56           2017-06-27  2018-02-07
45         3         98           2018-02-08  2018-08-25
45         3         55           2018-02-26  2018-08-26
45         6         44           2018-08-27  9999-12-31

第一條記錄的邏輯如下EndDate = 2018-02-07，下一條記錄將有EndDate + 1 day = 2018-02-08.對于 EndDate = StartDate 的記錄，它將在前一個 EndDate + 1 中都有.

The logic is like below for the first record the EndDate = 2018-02-07, the next record will have EndDate + 1 day = 2018-02-08. For the record with EndDate = StartDate it will have in both The previous EndDate + 1.

推薦答案

假設你的數據不是很離譜，可以使用lag():

Assuming your data is not very off, you can use lag():

with toupdate as (
      select t.*,
             lag(enddate) over (partition by employee order by startdate) as prev_enddate
      from t
     ) t
update toupdate
    set startdate = dateadd(day, 1, prev_enddate)
    where startdate <> dateadd(day, 1, prev_enddate);

如果您的數據有很多非常復雜的重疊，那么這會變得有點棘手.基本上，您希望保留結束日期并使用它們來計算開始日期——除了第一行之外的所有日期:

If your data has lots of really complicated overlaps, then this gets a bit trickier. Basically, you want to keep the end dates and use them to calculate the start dates -- for all but the first row:

with toupdate as (
      select t.*,
             lag(enddate) over (partition by employee order by enddate) as prev_enddate,
             row_number() over (partition by employee order by startdate) as seqnum
      from t
     ) t
update toupdate
    set startdate = dateadd(day, 1, prev_enddate)
    where seqnum <> 1 and
          startdate <> dateadd(day, 1, prev_enddate);

您需要 seqnum(或類似的東西)，因為在這種情況下，您不能保證最早的開始日期具有最早的結束日期.

You need seqnum (or something similar) because you are not guaranteed that the earliest start date has the earliest end date in this situation.

這篇關于通過在 BI_StartDate 中添加一天避免重疊的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！