问题描述
我有以下员工版本控制表:
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 中添加一天避免重叠的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!