Search code examples
sqlsql-servert-sqlsql-server-2016window-functions

Previous Year record in sql server

How do we get the previous year values in the current row.

example

id      year    amount
1001    2001    2400
1002    2003    3600
1003    2005    2300
1002    2004    1600

Desired Output

id      year    amount  prevyearAmount
1001    2001    2400    Null
1002    2003    3600    2400
1003    2005    2300    1600
1002    2004    1600    3600
Solution

  • I think you just want lag():

    select t.*, lag(amount) over (order by year) as prev_year_amount
from t;

    Note that you are asking for the previous year with data, as opposed to the previous year. In my opinion, you should have NULL for 2003 because there is no 2002 in the data.

    If that is what you really want, a self-join is easy enough:

    select t.*, tprev.amount as prev_year_amount
from t left join
     t tprev
     on tprev.year = t.year - 1