Testtest11 Testtest11 - 2 months ago 7
SQL Question

Difference between every 6th row in SQL

How I can calculate difference between every 6th row in SQL Server 2016? For example:

Col1 Col2 Diff
----- ---- ----
1 2 NULL
2 4 NULL
3 6 NULL
4 10 NULL
5 14 NULL
6 18 NULL
7 20 18
8 22 18
9 30 24

Answer

Use lag():

select t.*, col2 - lag(col2, 6) over (order by col1) as diff6
from t;

The second (and not frequently used) argument to lag() and lead() is offset. It returns NULL if the value is not there, which seems to be exactly what you want.