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

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

trkmml
Starting Member

Turkey
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

ID DATE DEBIT CREDIT BALANCE

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,


Regards,

sunitabeck
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
(
	SELECT *,
		ROW_NUMBER() OVER (ORDER BY Date,Id) AS RN
	FROM
		YourTable
)
SELECT
	a.Id, a.Date, a.Debit, a.Credit, b.Balance
FROM
	cte a
	CROSS APPLY
	(
		SELECT SUM(ISNULL(b.Debit,0)-ISNULL(b.Credit,0)) AS Balance
		FROM cte b
		WHERE b.RN <= a.RN
	)b
Go to Top of Page

trkmml
Starting Member

Turkey
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

sunitabeck
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

sodeep
Flowing Fount of Yak Knowledge

USA
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  
 New 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.06 seconds. Powered By: Snitz Forums 2000