CODEWITHSUNDEEP
sqlsql-server
tubby
tubby

Reputation: 2144

adding a column which calculates days until next event

For a given Id, I have a series of START_DATE. Along with displaying other columns, I want to add a new column which finds the difference between the START_DATE for an Id(person), and his next START_DATE.

Basically, want to find the interval between his present START_DATE and his next START_DATE, along with displaying other columns.

For example, the data looks as follows enter image description here

I tried doing this as follows : 

SELECT t.Id,t.START_DATE,(select top 1 s.START_DATE from dbo.MyTable t INNER JOIN dbo.MyTable s ON(t.Id = s.Id and t.START_DATE > s.START_DATE) GROUP BY t.patient_Id,t.START_DATE)

Upvotes: 0

Views: 37

Answers (2)

Tom H
Tom H

Reputation: 47454

If your version of SQL Server supports it then you can do this with the LEAD windowed function like this:

SELECT
    id,
    start_date,
    DATEDIFF(DAY, start_date, LEAD(start_date, 1) OVER (PARTITION BY id ORDER BY start_date)) AS days_until_next_start_date
FROM
    dbo.My_Table

Upvotes: 1

sagi
sagi

Reputation: 40481

I think this is what you need

SELECT t.Id,t.START_DATE,
       DATEDIFF(day,(select min(s.START_DATE) from dbo.MyTable s
                     where t.Id = s.Id and t.START_DATE < s.START_DATE),
                t.start_date)  as DifferenceInDays
FROM dbo.MyTable t

This will give you (in days, you can change it if you want) the difference between each date and the next one

Upvotes: 1

Related Questions