Search code examples
sqlsql-serverlagdifference

Calculate difference of two columns and output result in another column

Goal - Trying to calculate the difference between months based on two criteria, row number and Person ID.

I'm not sure how to get the difference

The Month Difference should be calculated based on the person ID and the row number,

higher the rownumber means the largest month value

Here is the data in rextester.

Current Data

Current Data

Result

Result

Solution

  • I think lag() does what you want:

    select rownum, personid, month,
       (month -
        lag(month) over (partition by personid order by month)
       ) as diff
from t;

    Note that for the first row, diff will be NULL rather than 'First Time'.

    You can test it here.