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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Running Sum

Author  Topic 

trkmml
Starting Member

2 Posts

Posted - 2012-12-20 : 14:59:34
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-20 : 15:14:50
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

2 Posts

Posted - 2012-12-20 : 15:34:44
i m using 2005 express edition :(
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-20 : 15:36:05
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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-20 : 16:10:53
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
Go to Top of Page
   

- Advertisement -