CODEWITHSUNDEEP
sqlsql-server
Ransom
Ransom

Reputation: 137

SQL: Calculating Number of Days Between Dates of One Column In Different Rows

With my data I have individuals taking an assessment multiple times at different dates. It looks something like this:

╔════════╦═══════════╦═══════════╦═══════╗
║ Person ║ ID Number ║ Date      ║ Score ║
║ John   ║ 134       ║ 7/11/2013 ║ 18    ║
║ John   ║ 134       ║ 8/23/2013 ║ 16    ║
║ John   ║ 134       ║ 9/30/2013 ║ 16    ║
║ Kate   ║ 887       ║ 2/28/2013 ║ 21    ║
║ Kate   ║ 887       ║ 3/16/2013 ║ 19    ║
║ Bill   ║ 990       ║ 4/18/2013 ║ 15    ║
║ Ken    ║ 265       ║ 2/12/2013 ║ 23    ║
║ Ken    ║ 265       ║ 4/25/2013 ║ 20    ║
║ Ken    ║ 265       ║ 6/20/2013 ║ 19    ║
║ Ken    ║ 265       ║ 7/15/2013 ║ 19    ║
╚════════╩═══════════╩═══════════╩═══════╝

I'd like it to have another column at the end that calculates the number of days since the first assessment for that person. I'd also settle for the number of days since the previous assessment for that person if that's easier.

Ideally it would look like this:

╔════════╦═══════════╦═══════════╦═══════╦══════════════════╗
║ Person ║ ID Number ║ Date      ║ Score ║ Days Since First ║
║ John   ║ 134       ║ 7/11/2013 ║ 18    ║ 0                ║
║ John   ║ 134       ║ 8/23/2013 ║ 16    ║ 43               ║
║ John   ║ 134       ║ 9/30/2013 ║ 16    ║ 81               ║
║ Kate   ║ 887       ║ 2/28/2013 ║ 21    ║ 0                ║
║ Kate   ║ 887       ║ 3/16/2013 ║ 19    ║ 16               ║
║ Bill   ║ 990       ║ 4/18/2013 ║ 15    ║ 0                ║
║ Ken    ║ 265       ║ 2/12/2013 ║ 23    ║ 0                ║
║ Ken    ║ 265       ║ 4/25/2013 ║ 20    ║ 72               ║
║ Ken    ║ 265       ║ 6/20/2013 ║ 19    ║ 128              ║
║ Ken    ║ 265       ║ 7/15/2013 ║ 19    ║ 153              ║
╚════════╩═══════════╩═══════════╩═══════╩══════════════════╝

Upvotes: 4

Views: 7844

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

You can use option with APPLY operator

1.difference between the current row date and the previous date

SELECT t1.*, 
       DATEDIFF(dd, ISNULL(o.[Date], t1.[Date]), t1.[Date]) AS [Days Since First]
FROM YourTable t1 OUTER APPLY (
                               SELECT TOP 1 [Date]
                               FROM YourTable t2
                               WHERE t1.[ID Number] = t2.[ID Number]
                                 AND t1.[Date] > t2.[Date]
                               ORDER BY t2.[Date] DESC
                               ) o

See example on SQLFiddle

2.number of days since the first assessment

SELECT t1.*, 
       DATEDIFF(dd, ISNULL(o.[Date], t1.[Date]), t1.[Date]) AS [Days Since First]
FROM YourTable t1 OUTER APPLY (
                               SELECT MIN(t2.[Date]) AS [Date]
                               FROM YourTable t2
                               WHERE t1.[ID Number] = t2.[ID Number]                                   
                               ) o

See example on SQLFiddle

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

I like Andomar's answer, but if you wanted to find both days between and total days since first you could do this:

SELECT a.*
        ,ISNULL(DATEDIFF(day,b.Date,a.Date),0)'Since Previous'
        ,datediff(day, min(a.Date) over (partition by a.[ID Number]), a.Date)'Since First'
FROM (select  *,ROW_NUMBER() OVER(PARTITION BY [ID Number] ORDER BY DATE)RowRank
      from    YourTable
      )a
LEFT JOIN (select  *,ROW_NUMBER() OVER(PARTITION BY [ID Number] ORDER BY DATE)RowRank
      from    YourTable
      )b
ON a.[ID Number] = b.[ID Number]
 AND a.RowRank = b.RowRank + 1

Demo: SQL Fiddle

Upvotes: 4

Andomar
Andomar

Reputation: 238296

select  *
,       datediff(day, min(Date) over (partition by [ID Number]), Date)
from    YourTable

Live example at SQL Fiddle.

Upvotes: 6

Related Questions