Yesterday I came across a situation, while generating a Report. It is just like a Rolling Balance problem. Consider a table containing columns ID,Invoice_Amount,Payment_Amount,CID with data as follows,
ID Invoice_Amount Payment_Amount CID
1 500 1500 A
2 300 1500 A
3 200 1500 A
1 200 1000 B
2 100 1000 B
Scenario:
For each customer there a fixed payment amount. Report need to generate an additional column,Which contain the difference between Payment_Amount and Invoice_Amount. This difference is used as the Payment _Amount for the next row. So it is a running balance situation. Look at this Result,you will know easily.
id invoice_amount payment_amount cid SUCESS
1 500 1500 A 1000
2 300 1500 A 700
3 200 1500 A 500
1 200 1000 B 800
2 100 1000 B 700
The query I used was,
SELECT a.id,
a.invoice_amount,
a.payment_amount,
a.cid,
Isnull(a.payment_amount - (SELECT
SUM(b.invoice_amount) + a.invoice_amount
FROM billdetails b
WHERE b.id < a.id
AND b.cid = a.cid),
a.payment_amount - a.invoice_amount) AS [SUCESS]
FROM billdetails a
So many people suggested me to use cursors and while loops,but I avoided,since it affect Performance.