Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Running Balance

Author  Topic 

x5-452
Starting Member

8 Posts
Posted - 2009-06-24 : 09:19:45
What's the best way in SQL to do a running balance on transaction amount. Example I have a table with the following fields:

ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT

And i want to show:

Account #
Transaction source,
Date,
Amount,
Running Balance (Current transaction amount minus last transaction amount).

Any suggestions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-06-24 : 09:23:41
It depends.
Are you using Microsoft SQL Server 4.2, 6.0, 6.5, 7, 2000, 2005 or 2008?

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

x5-452
Starting Member

8 Posts
Posted - 2009-06-24 : 10:21:03
SQL 2005
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-24 : 12:34:28
[code]SELECT t.*,t.TRANAMOUNT-COALESCE(p.TRANAMOUNT,0) AS RunningBal
FROM YourTable t
OUTER APPLY (SELECT TOP 1 TRANAMOUNT
FROM YourTable
WHERE ACCT=t.ACCT
AND TRANDATE< t.TRANDATE
ORDER BY TRANDATE DESC) p
[/code]
Go to Top of Page

x5-452
Starting Member

8 Posts
Posted - 2009-06-24 : 13:55:31
Hi TY! That works great but the running balance for the last line is the same amount as the transaction amount. The last column is the running balance and the second last column is the transaction amount.

Example:
1000 2009-01-01 00:00:00.000 402.00000 402.00000
1000 2009-05-28 00:00:00.000 0.00000 -402.00000
1000 2009-06-02 00:00:00.000 0.00000 0.00000
1000 2009-06-03 00:00:00.000 0.00000 0.00000
1000 2009-06-04 00:00:00.000 0.00000 0.00000
1000 2009-06-18 00:00:00.000 0.00000 0.00000
1000 2009-12-31 00:00:00.000 134218.53000 134218.53000
1000 2009-12-31 00:00:00.000 135599.92000 135599.92000
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-24 : 14:44:12
you will more than 1 record for same day?
Go to Top of Page

x5-452
Starting Member

8 Posts
Posted - 2009-06-24 : 15:53:02
Yes
Go to Top of Page

eonmantra
Starting Member

11 Posts
Posted - 2009-06-24 : 17:16:46
Are those all the fields in your table? Or more to the point, do you have an unique identifier field, like ID?

And does this look like the output you are looking for?


ACCT TRAN_SOURCE TRANDATE                TRANAMOUNT            RunBal
---- ----------- ----------------------- --------------------- ---------------------
1000 xxx         2009-01-01 00:00:00.000 402.00                402.00
1000 xxx         2009-05-28 00:00:00.000 0.00                  402.00
1000 xxx         2009-06-02 00:00:00.000 0.00                  402.00
1000 xxx         2009-06-03 00:00:00.000 0.00                  402.00
1000 xxx         2009-06-04 00:00:00.000 0.00                  402.00
1000 xxx         2009-06-18 00:00:00.000 0.00                  402.00
1000 xxx         2009-12-31 00:00:00.000 134218.53             134620.53
1000 xxx         2009-12-31 00:00:00.000 135599.92             270220.45
Go to Top of Page

x5-452
Starting Member

8 Posts
Posted - 2009-06-24 : 19:01:45
Yes that is the output i'm looking for.
Yes there is a unique identifier.
Go to Top of Page

eonmantra
Starting Member

11 Posts
Posted - 2009-06-24 : 20:13:49
This is the code I used to get the output. You will have to adapt it to your setup, and just make sure you substitute your unique id for my "TRAN_ID." The TRAN_ID field is used as a tie-breaker when you have records on the same date.


-- sets up a temp table using data you suggested
SET NOCOUNT ON;
DECLARE @T1 TABLE(TRAN_ID INT IDENTITY, ACCT VARCHAR(4), TRAN_SOURCE VARCHAR(3), TRANDATE DATETIME, TRANAMOUNT MONEY);

INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-01-01 00:00:00.000', 402.00000);
INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-05-28 00:00:00.000', 0.00000);
INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-06-02 00:00:00.000', 0.00000);	
INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-06-03 00:00:00.000', 0.00000);	
INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-06-04 00:00:00.000', 0.00000);	
INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-06-18 00:00:00.000', 0.00000);	
INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-12-31 00:00:00.000', 134218.53000);
INSERT INTO @T1(ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT) VALUES('1000', 'xxx', '2009-12-31 00:00:00.000', 135599.92000);

-- solution code starts here
WITH C AS 
(
	SELECT ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT, ROW_NUMBER() OVER(ORDER BY TRANDATE) AS TRAN_ID
	FROM @T1
)

SELECT ACCT, TRAN_SOURCE, TRANDATE, TRANAMOUNT,
  (SELECT SUM(B.TRANAMOUNT)
   FROM C AS B
   WHERE B.TRANDATE <= A.TRANDATE
   AND B.TRAN_ID <= A.TRAN_ID) AS RunBal
FROM C AS A
ORDER BY TRANDATE, TRAN_ID;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-06-25 : 13:47:49
quote:
Originally posted by x5-452

Yes that is the output i'm looking for.
Yes there is a unique identifier.

isnt this different from what you showed us before?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site