sql - Avoid overlapping in by adding a day in BI_StartDate

Question

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

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

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.

Answer 1

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);

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