通过在 BI_StartDate 中添加一天避免重叠

2021-09-10 00:00:00 sql tsql sql-server

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

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.

相关文章