Click here to Skip to main content
14,641,293 members
Rate this:
Please Sign up or sign in to vote.
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
Comments
CHill60 15-May-17 11:00am
   
The phrase you are looking for is "running total"
Rate this:
Please Sign up or sign in to vote.

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
   
Comments
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:
Please Sign up or sign in to vote.

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 :)
   
Comments
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
More details in my answer.
A4!
Suvendu Shekhar Giri 16-May-17 5:22am
   
Agree!
@OP, please follow the correct solution as suggested.

Thanks for the correction!
Maciej Los 16-May-17 10:55am
   
You're very welcome, my friend.
Cheers,
Maciej

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100