How to subtract column value in next row SQL server

Question

i want to subtract values in same column, this subtraction should be done on very next row.

i want to subtract record in row 1 and row 2 and so on (row2 x - row1 x)

Answer 1

You can use ANSI standard OLAP functions as below to get your desired result.

SELECT x,
       min(x) over(
                   ORDER BY id ROWS BETWEEN 1 following AND 1 following) - x AS RESULT
FROM table1;

The above query sorts the result by id and subtract row n from row n + 1 and displays result along with row n

Sample data along with Result:

x                   RESULT
-----------------------------------
318963.0000000000   -95.9999999990
318867.0000000010   -128.0000000000
318739.0000000010   128.0000000000
318867.0000000010   NULL

DEMO

Answer 2

You can use LEAD:

SELECT x
      ,lead(x, 1, 0) OVER (ORDER BY id) - x
From myTable;

You need to order the rows. In above example I have done this by ID but you can use whatever you have.

Answer 3

Here is other dynamic way is to use CTE with the help of row_number function will assign ranking to each rows.

;WITH CTE AS 
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RN FROM <table_name>
),
CTE1 AS
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RN FROM <table_name>
)
SELECT C1.X - C.X [x] FROM CTE C 
INNER JOIN CTE1 C1 ON C1.RN = C.RN+1

Explanation: The CTE will grab all of your records and apply a row number for each unique entry. Each additional entry will get an incrementing number.

Answer 4

You need an ID on your table to auto join your table. Use modulo operator to select half part with alias A an second part with alias B :

select
    A.*,
    B.*,
    B.X-A.X as DELTA
from
    theTable A inner theTable B on
        A.ID+1=B.ID
where
    A.ID % 2 = 1

EDIT, if you don't have an ID you an order clause to determine it :

with order_table (
    select
        ROW_NUMBER ()   
            OVER ( order by ??? ) as ID,
        *
    from
        theTable
)
select
    A.*,
    B.*,
    B.X-A.X as DELTA
from
    order_table A inner order_table B on
        A.ID+1=B.ID
where
    A.ID % 2 = 1

replace `???` by your criteria.

WARNING : Data in a sql table is not ordered. You can't easly preserve the insertion order without a specific columns like ID or Timesstamp...