'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              ║
╚════════╩═══════════╩═══════════╩═══════╩══════════════════╝
sqlsql-server


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions