sql - Duration between 2 dates based on another column

Question

I currently have a table of data that shows different steps in a process, with a date/time each step was carried out.

enter image description here

What I'm looking to do is add a column that calculates the time in minutes between each step, however it has to relate to the claimID, so in the image shown I would be looking for difference between each step for the top 4 results (as they share the same claimID), then the following 6 results, etc.

Can anyone help? I'm using SQL Server

Answer 1

Depending on what version of SQL Server you are using you can either use a self join or the lag window function (this should work in SQL Server 2012+):

select 
    claimid
    , statusid
    , statussetdate
    , coalesce(datediff(minute, 
               lag(statussetdate) over (partition by claimid order by statussetdate), 
               statussetdate
              ),0) as diff_in_minutes
from 
    your_table
order by 
    ClaimID
    , StatusSetDate;