how to display record with minus balance from above record

Question

how to deduct paid amount from previous record 

x = total amount 

y = paid amount (multiple)

Z = x - y (balance amount)

now i want record with detail and balace amount reflect the true outstanding amount for record.

if x = 100

and y = 10 , 20 , 30 , 40

so display will be 

x           y                z

100      10 90

100 20 70

100 30              40

100 40 0

---

NILESH MAKAVANA

Answer 1

Try this:

with data as (
   select *from ( values 
     ('A',1,100,10),
	 ('A',2,100,20),
	 ('A',3,100,30),
	 ('A',4,100,40),
     ('B',1,150,10),
	 ('B',2,150,20),
	 ('B',3,150,30),
	 ('B',4,150,40),
	 ('B',5,150,50)
  ) data(OrderID,SeqNo,TotalAmt,Payment)
)
select
  OrderID,
  SeqNo,
  TotalAmt,
  Payment,
  Balance = TotalAmt -
            ( select sum(Payment)
              from data tot
              where tot.OrderID = d.OrderID
                and tot.SeqNo  <= d.SeqNo
            )
from data d
order by OrderID, SeqNo
/*
OrderID SeqNo       TotalAmt    Payment     Balance
------- ----------- ----------- ----------- -----------
A       1           100         10          90
A       2           100         20          70
A       3           100         30          40
A       4           100         40          0
B       1           150         10          140
B       2           150         20          120
B       3           150         30          90
B       4           150         40          50
B       5           150         50          0
*/

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 2

actually i thought about the sub query and do that but's i have large data so it's time consuming for me. will help me out from it. is there any another way to get it.

thank you

 

---

NILESH MAKAVANA

Answer 3

Below is a faster version. But there is a catch: it only runs on SQL 2012.

You are right that the subquery that Pieter showed showed you will not be efficient. Performance is proportional to the square of number of rows per order. With many orders and few rows per order you may survive. Else, you will need to run a cursor. Tip: run it over SeqNo and handle all SeqNo in one go.

with data as (
   select *from ( values
     ('A',1,100,10),
    ('A',2,100,20),
    ('A',3,100,30),
    ('A',4,100,40),
     ('B',1,150,10),
    ('B',2,150,20),
    ('B',3,150,30),
    ('B',4,150,40),
    ('B',5,150,50)
  ) data(OrderID,SeqNo,TotalAmt,Payment)
)
select
  OrderID,
  SeqNo,
  TotalAmt,
  Payment,
  Balance = TotalAmt -
            SUM(Payment) OVER (PARTITION BY OrderID
                               ORDER BY SeqNo ROWS UNBOUNDED PRECEDING)
from data d
order by OrderID, SeqNo

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

is there possible query which work on sql server 2008 R2

i tried it on R2 but gives me error.

---

NILESH MAKAVANA

Answer 5

We are good, but not psychic:

1. Please show the exact query you ran; and
2. Show the error message you are getting.

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 6

Do you 'know' it's time consuming, or are you merely guessing. Most orders in real life do not have a large number of payments, and the inefficiency may be illusional; the true cost of a query is disk IO, but it is likely that the sub-queries in my proposal are entirely in cache and involve no disk IO at all. 'Guessing" about performance in a mug's game, played only by mugs. If you have not measured a performance problem, and diagnosed the bottleneck, then you are engaging in "premature optimization".

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 7

For the record, my performance expectations for this proposal are the following:

1. Disk IO: O(M+N), where N is the total number of orders and M is the total number of payments;
2. CPU: O(Mx(M/N)), N and M as above.

A good rule of thumb is that Disk IO is 100 to 1000 times more expensive than CPU cycles, so unless (M/N) is approaching 100, or perhaps more, no performance inefficency from the proposed correlated query need be expected.

---

"Premature optimization is the root of all evil." - Knuth

If I provoked thought, please click the green arrow

If I provoked Aha! please click Propose as Answer

We are here to learn, to share knowledge, and to earn points; all in about equal measure.

Answer 8

Yes, as I said the query I posted requires SQL 2012. For SQL 2008 R2, try the query that Pieter posted. If performance is not good enough, here is a solution that performs set-based iteration, and this is about as good as it can get on SQL 2008 R2.

CREATE TABLE #temp (OrderID char(1) NOT NULL,
                    SeqNo   int NOT NULL,
                    rowno   int NOT NULL,
                    TotalAmt int NOT NULL,
                    Payment  int NOT NULL,
                    Balance  int NOT NULL DEFAULT 0,
                    PRIMARY KEY NONCLUSTERED (OrderID, SeqNo),
                    UNIQUE CLUSTERED (rowno, OrderID)
)

;with data as (
   select *from ( values
    ('A',1,100,10),
    ('A',2,100,20),
    ('A',3,100,30),
    ('A',4,100,40),
    ('B',1,150,10),
    ('B',2,150,20),
    ('B',3,150,30),
    ('B',4,150,40),
    ('B',5,150,50)
  ) data(OrderID,SeqNo,TotalAmt,Payment)
)
INSERT #temp (OrderID, SeqNo, TotalAmt, Payment, rowno)
select
  OrderID,
  SeqNo,
  TotalAmt,
  Payment,
  row_number() OVER (PARTITION BY OrderID ORDER BY SeqNo DESC)
from data d
go
DECLARE @batchno int = 1

DECLARE cur CURSOR STATIC LOCAL FOR
   SELECT DISTINCT rowno FROM #temp
   WHERE  rowno >= 2
   ORDER BY rowno

OPEN cur

WHILE 1 = 1
BEGIN
  FETCH cur INTO @batchno
  IF @@fetch_status <> 0
     BREAK

  UPDATE this
  SET    Balance = prev.Balance + prev.Payment
  FROM   #temp this
  JOIN   #temp prev ON this.OrderID = prev.OrderID
  WHERE  this.rowno = @batchno
    AND  prev.rowno = @batchno - 1
END

DEALLOCATE cur
go
SELECT * FROM #temp
ORDER BY OrderID, SeqNo
go
DROP TABLE #temp

---

Erland Sommarskog, SQL Server MVP, [e-mail]