Another way to achieve that is to use:
LEAD[
^] and/or
LAG[
^] function.
For further details, please see:
SQL SERVER - Introduction to LEAD and LAG - Analytic Functions Introduced in SQL Server 2012 - Journey to SQL Authority with Pinal Dave[
^]
Note: SQL server 2012 and higher is required!
As i mentioned in the comment to the solution #1 by
Suvendu Shekhar Giri[
^], you have to self join tables on both fields:
idno and
tran as to be able to keep the relationship between
idno and
tran.
Check on below example:
DECLARE @MyTable TABLE(idno VARCHAR(3),[tran] INT, amount INT)
INSERT INTO @MyTable (idno, [tran], amount)
VALUES('001', 1, 200),
('001', 2, 500),
('001', 3, 1000),
('001', 4, 1700),
('002', 1, 100),
('002', 2, 300),
('002', 3, 1500),
('002', 4, 1750)
SELECT a.idno, a.[tran], a.amount, ISNULL(a.amount-b.amount,0) AS bal_difference
FROM @MyTable a
LEFT JOIN @MyTable b ON b.[tran]=a.[tran]-1
SELECT a.idno, a.[tran], a.amount, ISNULL(a.amount-b.amount,0) AS bal_difference
FROM @MyTable a
LEFT JOIN @MyTable b ON a.idno = b.idno AND b.[tran]=a.[tran]-1
Result #1 -
WRONG result
idno tran amount bal_difference
001 1 200 0
001 2 500 300
001 2 500 400
001 3 1000 500
001 3 1000 700
001 4 1700 700
001 4 1700 200
002 1 100 0
002 2 300 100
002 2 300 200
002 3 1500 1000
002 3 1500 1200
002 4 1750 750
002 4 1750 250
Result #2 -
CORRECT result
idno tran amount bal_difference
001 1 200 0
001 2 500 300
001 3 1000 500
001 4 1700 700
002 1 100 0
002 2 300 200
002 3 1500 1200
002 4 1750 250
Submit an article or tip