How to : Compute Blance in Cummulative Way (Ordered by Date) in SQL SERVER ( Else in : Vb.net OR CrustalReports )

Question

1.00/5 (2 votes)

See more:

Hello ,
I will try to make it brief. Here is my problem :
I'm developing an application in VB.Net using SQL SERVER Database( Store Management ).
Here are the tables related to my current problem :

1- Providers table : Each provider has a BALANCE ( The difference between My purchases from this provider and The Payments of my Purchases for the same Provider ).

2- Purchases Table : Each Purchase operation has a DATE and an AMOUNT. (FK = Provider ID)

3- Payments Table : Each Payment operation has a DATE and an AMOUNT. (FK = Provider ID)

From these three Tables my client wants to get the History of Computing the BALANCE of each provider as following :

BALANCE SITUATION OF THE PROVIDER : X
_______________________________________

DATE......../..PURCHASES ......./..PAYMENTS........./.....BALANCE
____________________________________________________________________
01/01/2013 ./....... 1200 ....../...... Null ......./..... - 1200    (Initial Balance= 0)

03/01/2013 ./....... 800  ....../...... Null ......./..... - 2000 

10/01/2013 ./....... Null ....../...... 2000 ......./...........0

15/01/2013 ./....... 1500 ....../...... 1500 ......./...........0

18/01/2013 ./....... 3000 ....../...... 1000 ......./..... - 2000  

20/01/2013 ./....... Null ....../...... 1000 ......./...... -1000

23/01/2013 ./....... Null ....../...... 1000 ......./.......... 0

----------------------------------------------------------------------------

I hope the table looks good.

NB :

1.The Date Column makes together the Purchases and the Payments Dates.
2. Current BALANCE = (Previous BALANCE) - (Current PURCHASE AMOUNT) + (CURRENT PAYMENT AMOUNT)
Example : In 18/01/2013 : BALANCE = 0 - 3000 + 1000 = -2000

I tried many ways but in vain .

SO How Can I DO That : In SQL SERVER ( Or If there is a solution in VB.NET and Crystal Reports) ?

If there is a mistake or something that needs to be clear Please mention it .
I will really appreciate your help Thank you in advance : )

Posted 15-Sep-13 5:43am

Member 10267912

Updated 16-Sep-13 2:21am

Maciej Los

v7

Add a Solution

Comments

CHill60 15-Sep-13 11:49am

"I tried many ways but in vain." .... what have you tried?

Member 10267912 15-Sep-13 15:47pm

I created an other table in witch I insert date , Payment and Purchase amount whenever I insert a payment or purchase transaction. But its not the solution I am looking for. I need just a view With a SELECT Clause So I wont care about the updates made in The Purchase or Payment Tables to get the right Balance in the right moment.

The Hard part for me is how Can I perform this : Current BALANCE = (Previous BALANCE) - (Current PURCHASE AMOUNT) + (CURRENT PAYMENT AMOUNT) . I hope you got me And sorry for Updating a lot My posts .

CHill60 15-Sep-13 13:29pm

Ok so you've just bumped your post by editing it, but what have you tried so far to solve this

syed shanu 16-Sep-13 0:39am

Chk my article its simpler to your request ,I have done for Inventory management using SQL Triggers.
http://www.codeproject.com/Articles/643104/Inventory-Management-Using-SQL-Trigger

Maciej Los 16-Sep-13 9:05am

Post sample data...

Member 10267912 18-Sep-13 8:52am

The table is now clear I guess after updating by Admins . Now I only need how to perferm this : Current BALANCE = (Previous BALANCE) - (Current PURCHASE AMOUNT) + (CURRENT PAYMENT AMOUNT) for each row .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-09-18T12:04:00

Here is an example:

SQL

SET DATEFORMAT dmy;
--create temporary table
CREATE TABLE #purchases (PurID INT IDENTITY(1,1), ProviderId INT, Date DATETIME, Amount DECIMAL(8,2))
--insert data
INSERT INTO #purchases (ProviderId, Date, Amount)
SELECT 1, '01/01/2013', 1200
UNION ALL SELECT 1, '03/01/2013', 800
UNION ALL SELECT 1, '15/01/2013', 1500
UNION ALL SELECT 1, '18/01/2013', 3000
--create temporary table
CREATE TABLE #payments (PayID INT IDENTITY(1,1), ProviderId INT, Date DATETIME, Amount DECIMAL(8,2))
--insert data
INSERT INTO #payments (ProviderId, Date, Amount)
SELECT 1, '10/01/2013', 2000
UNION ALL SELECT 1, '15/01/2013', 1500
UNION ALL SELECT 1, '18/01/2013', 1000
UNION ALL SELECT 1, '20/01/2013', 1000
UNION ALL SELECT 1, '23/01/2013', 1000

--declare and set varaible to store columns name
DECLARE @cols VARCHAR(300)
SET @cols = '[Purchase],[Payment]'
--declare and set variables
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(4000)
DECLARE @ft VARCHAR(MAX)
--source for pivot
SET @dt ='SELECT ProviderId, Date, Amount, ''Purchase'' AS [Operation] ' +
		'FROM #purchases ' +
		'UNION ALL ' +
		'SELECT ProviderId, Date, Amount, ''Payment'' AS [Operation] ' +
		'FROM #payments ' 
--pivot table
SET @pt = 'SELECT ProviderId, Date, ' + @cols + ' ' +
		'FROM (' + @dt + ') AS DT ' +
		'PIVOT(SUM(Amount) FOR Operation IN(' + @cols + ')) AS PT '
--declare temporary table to store result of pivot
CREATE TABLE #tmp (OperationId INT, ProviderId INT, Date DATETIME, Purchase DECIMAL(8,2), Payment DECIMAL(8,2))
--insert data into temporary table
--use COALESCE to replace NULL values with zeros
SET @ft = 'INSERT INTO #tmp(OperationId, ProviderId, Date, Purchase, Payment) ' +
			'SELECT ROW_NUMBER() OVER(PARTITION BY ProviderId ORDER BY Date) AS OperationID, ProviderId, Date, COALESCE(Purchase*-1,0) AS Purchase, COALESCE(Payment,0) AS Payment ' +
			'FROM (' + @pt + ') AS T'
EXEC(@ft)
--view balance
SELECT ST.OperationId, ST.ProviderId, ST.Date, AT.Purchase, AT.Payment, ST.CumPur + ST.CumPay AS CurrentBalance
FROM (
	SELECT t1.OperationId, t1.ProviderId, t1.Date, SUM(t2.Purchase) AS CumPur, SUM(t2.Payment) AS CumPay
	FROM #tmp AS t1 INNER JOIN #tmp AS t2 ON t1.OperationId>=t2.OperationId AND t1.ProviderId = t2.ProviderId
	GROUP BY t1.OperationId, t1.ProviderID, t1.Date
	) AS ST INNER JOIN #tmp AS AT ON ST.OperationId = AT.OperationId AND ST.ProviderId = AT.ProviderId
ORDER BY ST.OperationId
--delete temporary tables
DROP TABLE #tmp
DROP TABLE #purchases 
DROP TABLE #payments

Result:

CSS

OperationID   ProviderId  Date        Purchase    Payment    CurrentBalance
1             1           2013-01-01   -1200.00    0.00       -1200.00
2             1           2013-01-03   -800.00     0.00       -2000.00
3             1           2013-01-10   0.00        2000.00    0.00
4             1           2013-01-15   -1500.00    1500.00    0.00
5             1           2013-01-18   -3000.00    1000.00    -2000.00
6             1           2013-01-20   0.00        1000.00    -1000.00
7             1           2013-01-23   0.00        1000.00    0.00

The rest belongs to you ;)