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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Running Sum
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 12/20/2012 :  14:59:34  Show Profile  Reply with Quote
Hi, I am working on a software primarily related to accounting with visual basic

Many of the problems and proposed several solutions for running sum, but I have read almost all of these interpretations,

have applied when running sum is a unique ID field. but almost all of the functioning of accounting reports are in chronological order. So he questioned what was the balance on 05.05.2012.

ID of the process is done, if the user enters a process backward a few things since then, the transaction date is old, but the ID is new, are experiencing problems. eg


1 02.02.2012 100.00 0.00 100.00

6 04.04.2012 0.00 150.00 -50.00

3 02.05.2012 70.00 0.00 20.00

4 02.05.2012 80.00 0.00 100.00

2 06.06.2012 120.00 0.00 220.00

must balance the figure above.

if ID = 2 record, the record date 06/06/2012 and if the ID = 6, date 04/04/2012, the date the order is made if the listing is experiencing trouble getting balance. 2 records in the same day when the balance at worst formula breaks down. The big point is that I hang out in my project.

the only solution is to use a temporary table or writing about it in Visual Basic seems to calculate. the use of temporary table is not a solution, but unfortunately also very fast.

Sorry for my english. I use Google Translate
Thanks in advance,


Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  15:14:50  Show Profile  Reply with Quote
If you are using SQL 2012, this would be very easy.

Since you are on SQL 2008, all the available methods are either slow or makes use of undocumented features.

Here is one way to this:
;WITH cte AS
	a.Id, a.Date, a.Debit, a.Credit, b.Balance
	cte a
		SELECT SUM(ISNULL(b.Debit,0)-ISNULL(b.Credit,0)) AS Balance
		FROM cte b
		WHERE b.RN <= a.RN
Go to Top of Page

Starting Member

2 Posts

Posted - 12/20/2012 :  15:34:44  Show Profile  Reply with Quote
i m using 2005 express edition :(
Go to Top of Page

Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  15:36:05  Show Profile  Reply with Quote
The code I posted will work in SQL 2005 Express. You should replace "YourTable" with your actual table name.
Go to Top of Page

Flowing Fount of Yak Knowledge

7174 Posts

Posted - 12/20/2012 :  16:10:53  Show Profile  Reply with Quote
Or this

declare @t table (ID int,DATE datetime, DEBIT decimal(10,2), CREDIT decimal(10,2))
insert @t select 1,'02/02/2012',100.00,0.00
insert @t select 6,'04/04/2012',0.00,150.00
insert @t select 3,'02/05/2012',70.00,0.00
insert @t select 4,'02/05/2012',80.00,0.00
insert @t select 2,'06/06/2012',120.00,0.00

;with cte1 as
select *,ROW_NUMBER() OVER (Order by Date) as seq
from @t
, cte2 as
select [DATE], DEBIT, CREDIT,Balance = cast(DEBIT - CREDIT as decimal(10,4)), seq 
from cte1 where seq = 1
union all
select cte2.[DATE], cte2.DEBIT, cte2.CREDIT, Balance = cast(cte2.Balance + cte1.DEBIT - cte1.CREDIT as decimal(10,4)), cte2.seq + 1 
from cte1 join cte2 on cte1.seq = cte2.seq + 1

select cte1.ID,cte1.DATE, cte1.DEBIT, cte1.CREDIT, cte2.Balance
from cte2 JOIN cte1 ON cte2.seq = cte1.seq
order by cte1.DATE

Edited by - sodeep on 12/20/2012 16:12:22
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000