CODEWITHSUNDEEP
sql-server
Mandy
Mandy

Reputation: 83

Display end date minus one day by comparing next start date for same project

I have one table in SQL where I want to update the End date which should be minus one day of Start date of next row. Also condition is that row should be of same project with "same resources" for same employee.

enter image description here

For example, in above table for project ignition End date of first record should be minus one day of Start date of second record. Same for another project also. I have tried the self join for this as I want to compare two rows of same table but it does not work for exact result for me :-(

Upvotes: 0

Views: 391

Answers (2)

wiretext
wiretext

Reputation: 3342

here i put the logic how can we achieve it please customize it according to you i hope this will help you 

declare @temp table
(name nvarchar(44),
startdate date,
enddate date
)

insert into @temp values ('one', '2015-07-01', '2015-07-31')
insert into @temp values ('one', '2015-01-16', '2015-12-31')
insert into @temp values ('two', '2015-07-01', '2015-07-31')
insert into @temp values ('two', '2015-07-01', '2015-11-30')

;WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.name),
p.name,
p.startdate,
p.enddate
FROM @temp p
)

SELECT CTE.name,
--CTE.startdate,CTE.enddate,
--prev.enddate PreviousValue,
--nex.enddate NextValue,
DATEDIFF(day,CTE.enddate,nex.enddate) diff
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1

Upvotes: 0

shA.t
shA.t

Reputation: 16968

I think you can use LEAD function like this:

UPDATE yourTable
SET EXPECTEDENDDATE = ISNULL(DATEADD(DAY, 1, 
    LEAD(EXPECTEDSTARETDATE) OVER (PARTITION BY ProjectName 
                                   ORDER BY EXPECTEDSTARETDATE), EXPECTEDSTARETDATE);

Upvotes: 1

Related Questions