sql server - Subtract values from subsequent row

Question

I have a situation where I need to generate second column by subtracting from the first column. Is there any way to achieve this.

question from:https://stackoverflow.com/questions/66064665/subtract-values-from-subsequent-row

Answer 1

It looks like you want lead():

select col1, 
       lead(col1) over (order by col1) - 1
from t;

If you want 395 as the final value, you can add a default value:

select col1, 
       lead(col1, 1, 396) over (order by col1) - 1
from t;