Help Needed in Calculating Balance for each entry

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 2000 Forums

Transact-SQL (2000)

Help Needed in Calculating Balance for each entry

Author

Topic

harissarwar
Starting Member

15 Posts

Posted - 2008-06-29 : 09:23:14

I have a table which contains the amount of invoice. Lets call Invoices. Another table contains the amount payed by the cutomer on a specific date. Lets call it Payments.

In the form where i have to show invoices of a certain cutomer i also need to show another field balance along with each invoice entry. This balance is basically (amount of all the invoices before this entry - sum of all the payments received from that customer).

How i can achive this result Efficiently

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-29 : 09:26:03

please post your table structure (DDL), same sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

harissarwar
Starting Member

15 Posts

Posted - 2008-06-29 : 09:44:26

Table:CasingInvoices
Columns:ID, Amount, InvoiceDate, CustomerID

Table:CasingPayments
Columns:ID, Amount, PaymentDate, Detail, MethodID, CustomerID

MethodID is basically foreign key from PaymentMethods

I need to display it like this
Amount, InvoiceDate, Balance

Depending on the customer selected these invoices have to be displayed.

harissarwar
Starting Member

15 Posts

Posted - 2008-06-29 : 10:02:14

Sample Data
CasingInvoices
ID Amount InvoiceDate CustomerID
8 11235 6/1/2008 84
31 61480 6/8/2008 84
33 37180 6/10/2008 84
35 127580 6/12/2008 84
CasingPayments
ID Amount Pay.Date MID CustomerID
2 12000 6/2/2008 1 84
4 20000 6/9/2008 2 84
5 50000 6/11/2008 1 84

Result Wanted Like this
Amount InvoiceDate Balance
11235 6/1/2008 0
61480 6/8/2008 765
37180 6/10/2008 -40715
127580 6/12/2008 -27895

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-29 : 10:04:49

How about some sample data and the expected output based on the sample data ?

KH
[spoiler]Time is always against us[/spoiler]

harissarwar
Starting Member

15 Posts

Posted - 2008-06-29 : 10:06:38

Sample Data is here

CasingInvoices
ID Amount InvoiceDate CustomerID
8 11235 6/1/2008 84
31 61480 6/8/2008 84
33 37180 6/10/2008 84
35 127580 6/12/2008 84

CasingPayments
ID Amount Pay.Date MID CustomerID
2 12000 6/2/2008 1 84
4 20000 6/9/2008 2 84
5 50000 6/11/2008 1 84

Result Wanted Like this

Amount InvoiceDate Balance
11235 6/1/2008 0
61480 6/8/2008 765
37180 6/10/2008 -40715
127580 6/12/2008 -27895

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-29 : 10:16:13

how do you identify which payment is for which invoices ?

KH
[spoiler]Time is always against us[/spoiler]

harissarwar
Starting Member

15 Posts

Posted - 2008-06-29 : 10:22:49

we dont need to identify which payment is against which invoice.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-29 : 10:33:43

[code]
SELECT i.ID, i.Amount, i.InvoiceDate,
balance = (SELECT SUM(Amount) FROM CasingInvoices x
WHERE x.CustomerID = i.CustomerID AND x.InvoiceDate < i.InvoiceDate)
- (SELECT SUM(Amount) FROM CasingPayments x
WHERE x.CustomerID = i.CustomerID AND x.Pay_Date < i.InvoiceDate)
FROM CasingInvoices i
[/code]

KH
[spoiler]Time is always against us[/spoiler]

harissarwar
Starting Member

15 Posts

Posted - 2008-06-29 : 10:54:21

Thankyou khtan
what i am worried about is that as invoices and payment entries will increase wont this query response will be slow.
Because for each entry all the previous records will have to be searched for calculating the sum of invoices and then sum of payments.
Cant' we use previous row balance in optimizing this query?
If we can i don't know how to get the previous record entry.
Can u help me further in this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-29 : 13:44:45

[code]SELECT m1.Amount,m1.InvoiceDate,
m1.PayAmount-m2.InvAmount AS Balance
FROM
(SELECT i.Amount,i.InvoiceDate,SUM(p.Amount) AS PayAmount
FROM CasingInvoices i
LEFT JOIN CasingPayments p
ON p.PayDate<i.InvoiceDate
GROUP BY i.Amount,i.InvoiceDate)m1
INNER JOIN
(SELECT i1.Amount,i1.InvoiceDate,SUM(i2.Amount) AS InvAmount
FROM CasingInvoices i1
LEFT JOIN CasingInvoices i2
ON i2.InvoiceDate<i1.InvoiceDate
GROUP BY i1.Amount,i1.InvoiceDate)m2
ON m1.InvoiceDate=m2.InvoiceDate[/code]

harissarwar
Starting Member

15 Posts

Posted - 2008-06-29 : 14:46:04

Thanks visakh16
How should i handle NULL values in you query?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-29 : 15:42:23

with coalesce or Isnull function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 00:08:49

[code]SELECT m1.Amount,m1.InvoiceDate,
COALESCE(m1.PayAmount-m2.InvAmount,0) AS BalanceFROM
(SELECT i.Amount,i.InvoiceDate,SUM(p.Amount) AS PayAmount
FROM @CasingInvoices i
LEFT JOIN @CasingPayments p
ON p.PayDate<i.InvoiceDate
GROUP BY i.Amount,i.InvoiceDate)m1
INNER JOIN
(SELECT i1.Amount,i1.InvoiceDate,SUM(i2.Amount) AS InvAmount
FROM @CasingInvoices i1
LEFT JOIN @CasingInvoices i2
ON i2.InvoiceDate<i1.InvoiceDate
GROUP BY i1.Amount,i1.InvoiceDate)m2
ON m1.InvoiceDate=m2.InvoiceDate[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources