CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Mangesh Ukade
Mangesh Ukade

Reputation: 35

Subtract previous row value to current row

I have the following table:

id  value  acc_no 
-----------------
1     12     1 
2     14     1 
3     15     1 
4     10     2 
5     16     2 
6     19     1 
7      7     3 
8     24     2

Expected output 

id  value  acc_no   result 
------------------------------
1     12     1          12(current row values of acc_no=1) 
2     14     1          2(14 (current row values)-12(previous row value of acc_no=1))  
3     15     1          1(15-14) 
4     10     2          10(current row values of acc_no=2) 
5     16     2          6(16 (current row values)-12(previous row value of acc_no=2)) 
6     19     1          4(19(current row values)-15(previous row value of acc_no=1)) 
7     7      3           7(current row values of acc_no=3) 
8     24     2          8(24(current row values)-16(previous row value of acc_no=2))

I tried this query:

select 
    id, value,
    acc_no,
    (value - (select value from tb_acc t1 where t1.id = t.id - 1)) as result 
from 
    tb_acc t

But I didn't get the proper output as expected

Upvotes: 3

Views: 11087

Answers (4)

wnutt
wnutt

Reputation: 549

You just need a windowed SUM:

SELECT 
    id  
    ,value  
    ,acc_no
    ,value - isnull(sum([value])  over (partition by acc_no order by id rows between 1 preceding and 1 preceding ), 0) as result
FROM tb_acc t
order by id

Upvotes: 0

Ilyes
Ilyes

Reputation: 14926

You can do like

Option one: Using LAG() function (I just notice you are using 2008 but I post it for other readers as well)

SELECT *,
       Value - LAG(Value, 1, 0) OVER(PARTITION BY acc_no ORDER BY ID) Result
FROM T
ORDER BY ID;

Option two: Using a CTE and a window function + ISNULL()

WITH CTE AS
(
  SELECT *,
         ROW_NUMBER() OVER(PARTITION BY acc_no ORDER BY id) RN
  FROM T
)
SELECT T1.id,
       T1.value,
       T1.acc_no,
       T1.value - ISNULL(T2.value, 0) Result
FROM CTE T1 LEFT JOIN CTE T2
     ON T1.acc_no = T2.acc_no
        AND 
        T1.RN = T2.RN + 1
ORDER BY T1.id;

Live Demo

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Using window functions:

;WITH CTE AS 
(
   SELECT id, value, acc_no, 
          ROW_NUMBER() OVER (PARTITION BY acc_no ORDER BY id) AS seq
   FROM tb_acc
)
SELECT t1.*, t1.value - COALESCE(t2.value, 0)
FROM CTE AS t1
LEFT JOIN CTE AS t2 ON t1.acc_no = t2.acc_no AND t1.seq = t2.seq + 1

Upvotes: 2

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

DECLARE @Test TABLE (
    id      int,
    value   int,
    acc_no  int
) 
INSERT @Test(id, value, acc_no)
VALUES
(1, 12, 1),
(2, 14, 1), 
(3, 15, 1),
(4, 10, 2),
(5, 16, 2),
(6, 19, 1),
(7,  7, 3),
(8, 24, 2)

SELECT id, t.value, acc_no, t.value - ISNULL(v.value, 0) AS result
FROM @Test t
    OUTER APPLY (
        SELECT TOP (1) value
        FROM @Test
        WHERE id < t.id
            AND acc_no = t.acc_no
        ORDER by id DESC
    ) v

Upvotes: 7

Related Questions