CODEWITHSUNDEEP
sql-server
Faris Ahmed
Faris Ahmed

Reputation: 29

Count Years of Employment

I'm trying to find a way using sql to update the employment enddate for any user if the second position was started the next day after the first position was ended. thanks in advance!

Here's my data:

DECLARE @EmploymentDateRanges TABLE
        (
            ID INT IDENTITY (1, 1),
            StartDate DATETIME,
            EndDate DATETIME
        )

INSERT INTO @EmploymentDateRanges
VALUES ('11/1/2018', null)

INSERT INTO @EmploymentDateRanges
VALUES ('8/24/1995', '10/31/2018')

INSERT INTO @EmploymentDateRanges
VALUES ('5/25/1990', '8/24/1994')

As you can see, the startdate in the first insert statement is the next day after the enddate in the second insert statement.

My expected result is to update only the enddate in the second insert statement and set it to null.

The end date in the third insert statement shouldn't be changed since the different between the enddate in the third statement and startdate in the second statement is more than 1 day

Upvotes: 0

Views: 75

Answers (1)

sgeddes
sgeddes

Reputation: 62841

If I'm understanding correctly, you can use a self join and dateadd:

update e
set e.enddate = null
from EmploymentDateRanges e 
   join EmploymentDateRanges e2 on dateadd(day, 1, e.enddate) = e2.startdate

This adds 1 day to the enddate and sees if there are any corresponding records with that startdate. If so, it updates the enddate to null.

Another option would be to use exists:

update e
set e.enddate = null
from EmploymentDateRanges e 
where exists (select 1
              from EmploymentDateRanges e2
              where dateadd(day, 1, e.enddate) = e2.startdate)

Upvotes: 1

Related Questions