Thread: Running Sum In SQL
12-20-12, 15:03 #1Registered User
- Join Date
- Dec 2012
Unanswered: Running Sum In SQL
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,
12-20-12, 16:58 #2Resident Curmudgeon
Provided Answers: 54Code:
- Join Date
- Feb 2004
- In front of the computer
DECLARE @t TABLE ( [ID] INT , [DATE] DATE , [DEBIT] MONEY , [CREDIT] MONEY ) INSERT INTO @t VALUES (1, '2012-02-02', 100.00, 0.00) , (2, '2012-06-06', 120.00, 0.00) , (3, '2012-05-02', 70.00, 0.00) , (4, '2012-05-02', 80.00, 0.00) , (6, '2012-04-04', 0.00, 150.00) SELECT [ID], [DATE], [DEBIT], [CREDIT] , Sum([DEBIT] - [CREDIT]) OVER (ORDER BY [DATE], [ID]) AS [BALANCE] FROM @t
Your problem is a great example of using OVER Clause (Transact-SQL) to solve a running balance problem.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.