Calculate time difference between rows?

Question

I hope someone can help. I have a table that looks like the following.

ModifyDate| ModifiedBy | TaskID |
2018-02-05| Bob        | 55444  |
2018-02-06| Lily       | 55444  |
2018-02-08| Sarah      | 55444  |

For each row I need to calculate the time difference in days between Modifydate for each TaskID.

For example my output should look like the below:

ModifyDate| ModifiedBy | TaskID | Time
2018-02-05| Bob        | 55444  |  1
2018-02-06| Lily       | 55444  |  2
2018-02-08| Sarah      | 55444  | NULL

There is a NULL on Sarah as she was the last person to modify the Task.

Does this make sense? Hopefully someone can help

Thanks!

https://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field — Eray Balkanli, Feb 08 '18 at 14:27
@ErayBalkanli that's not applicable to SQL Server 2012. You no longer need to JOIN to get the previous row — Panagiotis Kanavos, Feb 08 '18 at 14:30

score 4 · Answer 1 · answered Feb 08 '18 at 14:26

Something like this should work:

SELECT ModifyDate, ModifiedBy, TaskID,
       DATEDIFF(DAY, ModifyDate,
                     LEAD(ModifyDate) OVER (PARTITION BY TaskID  
                                            ORDER BY ModifyDate))
FROM mytable

Demo here

Calculate time difference between rows?

1 Answers1