CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Dealing with Dates from multiple rows

I need to generate an End Date for each row with the same ID value to be the date-1 from the next record, and if there isn't a next row for the ID then the End Date should be null.

For example for ID 513, the first row would be 2008-01-01 and 2010-04-16, and the second row would be 2010-04-17, 2011-04-25.

I'm not sure how I could go about achieving this without a cursor.

Create Table #Temp
(
    ID int,
    Amount money,
    StartDate datetime
)

insert into #Temp
(
    ID,
    Amount,
    StartDate
)
select 513,240.00,'2008-01-01 00:00:00' union all
select 513,240.00,'2010-04-17 00:00:00' union all
select 513,265.00,'2011-04-26 00:00:00' union all
select 513,275.00,'2012-04-17 00:00:00' union all
select 513,285.00,'2013-04-22 00:00:00' union all
select 513,325.00,'2015-06-15 00:00:00' union all
select 513,335.00,'2017-06-15 00:00:00' union all
select 514,280.00,'2001-01-22 00:00:00' union all
select 514,280.00,'2010-06-09 00:00:00' union all
select 515,240.00,'2019-01-01 00:00:00' union all
select 515,240.00,'2010-04-17 00:00:00' union all
select 515,265.00,'2011-04-26 00:00:00' union all
select 515,275.00,'2012-04-17 00:00:00' union all
select 515,285.00,'2013-04-22 00:00:00' union all
select 515,325.00,'2015-06-15 00:00:00' union all
select 515,335.00,'2017-06-15 00:00:00'

select * from #Temp

drop table #Temp

Upvotes: 1

Views: 59

Answers (4)

Rima
Rima

Reputation: 1455

select t1.* ,  dateadd(day,-1,t2.dt) enddate
from  (select row_number()over(partition by ID order by ID) srno,  ID,  StartDate dt from #Temp) t1 
left join (select row_number()over(partition by ID order by ID) srno,   ID,  StartDate dt from #Temp)t2 on  t1.srno =(t2.srno-1)and  t1.ID = t2.ID

Upvotes: 0

sabhari karthik
sabhari karthik

Reputation: 1371

FOR SQL Server 2005+: 

   WITH CTE AS(
        SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY StartDate) as row_num,
        ID, Amount, StartDate FROM #Temp 
    )
    SELECT
    prev.ID,
    prev.Amount,
    prev.StartDate,
    DATEADD(DAY, -1, ISNULL(prev.StartDate,1)) AS EndDate
    FROM CTE prev
    LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1

Upvotes: 0

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

You could use lead and it works as you expect in the requirement,

select *, lead(StartDate-1) over(partition by id order by id,startdate) as EndDate 
from #Temp

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

You can use LEAD() and DATEADD() to achieve your result:

SELECT *, DATEADD(DAY, -1, LEAD (StartDate, 1) OVER (PARTITION BY ID ORDER BY StartDate)) AS ENDDATE
FROM #Temp

Demo on db<>fiddle

Upvotes: 3

Related Questions