SQL SERVER – RollingBalanceProblem-No Cursors,No loop

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.

print

Leave a Reply

Your email address will not be published. Required fields are marked *


× 3 = fifteen