complex Subtract in sql server query

Question

Hello,

I have table like following

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

i need to get Subtract of 20 - 15 and 30 - 25 and 40 - 35 and so on by select 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 a join to search for the previous row:

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