Complex Subtract in sql server query

Question

I have a table like the following

  PK num1 num2 numsdiff
 1 10 15?
 2 20 25?
 3 30 35?
 4 40 45? 

I need to get a subtraction of 20 - 15 and 30 - 25 and 40 - 35, etc. by selecting a query from this table.

any ideas ?.

thanks

Answer 1

WITH q AS ( SELECT *, ROW_NUMBER() OVER ORDER BY (num1) AS rn FROM mytable ) SELECT qc.*, qc.num1 - qp.num2 FROM q qc LEFT JOIN q qp ON qp.rn = qc.rn - 1 

Answer 2

You can use the join to search for the previous line:

 declare @t table (pk int, num1 int, num2 int) insert into @t select 1, 10, 15 union all select 2, 20, 25 union all select 3, 30, 35 union all select 4, 40, 45 select cur.pk, cur.num1 - prev.num2 from @t cur join @t prev on cur.pk = prev.pk + 1