14,641,293 members
Rate this:
See more:
Dear Expert,

Need to generate the following output in differences.

idno tran amount bal_difference
-------------------------------------------
001 1 200 0
001 2 500 300
001 3 1000 500
001 4 1700 700

The calculation is as follows

1. The amount of the first record stays as zero
2. How do I use sql statement to generate the expected output (i.e bal_difference)

Thanks

What I have tried:

This is a peculiar issue, checked for possibles not finding any.
Posted
Updated 15-May-17 22:01pm
v2
CHill60 15-May-17 11:00am

The phrase you are looking for is "running total"

Rate this:

## Solution 2

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)

--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 b.[tran]=a.[tran]-1

--2
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```
Suvendu Shekhar Giri 16-May-17 5:24am

5ed!
Thanks for correcting and the links suggested.
Maciej Los 16-May-17 10:50am

Thank you.
Rate this:

## Solution 1

You can make use of `SELF JOIN` to do this.
Try something like following-
```SELECT a.id, a.[no], a.tran_amount, ISNULL(a.tran_amount-b.tran_amount,0) AS bal_difference
FROM #MyTable a
LEFT JOIN #MyTable b ON b.no=a.no-1```

COMPLETE TESTED QUERY
```CREATE TABLE #MyTable(id VARCHAR(3),[no] INT, tran_amount INT)

INSERT INTO #MyTable
SELECT '001', 1, 200
UNION
SELECT '001', 2, 500
UNION
SELECT '001', 3, 1000
UNION
SELECT '001', 4, 1700

SELECT a.id, a.[no], a.tran_amount, ISNULL(a.tran_amount-b.tran_amount,0) AS bal_difference
FROM #MyTable a
LEFT JOIN #MyTable b ON b.no=a.no-1```

Hope, it helps :)
CHill60 15-May-17 11:19am

Beat me to it. 5'd
Suvendu Shekhar Giri 15-May-17 11:35am

Thanks :)
Maciej Los 16-May-17 4:01am

Seems, you have to change your relationship between self-joined tables into:
`ON b.no=a.no-1 AND b.id=a.id`
A4!
Suvendu Shekhar Giri 16-May-17 5:22am

Agree!

Thanks for the correction!
Maciej Los 16-May-17 10:55am

You're very welcome, my friend.
Cheers,
Maciej