0

This is my table structure

 item |Transaction Type| Qty_IN | Qty Out |Transaction Sequence
------|----------------|--------|-----------------------
 item1| Beg. Balance   |   15   |    0    |  1
 item1| Received Item  |   5    |    0    |  2
 item1| Transfer Item  |   0    |    2    |  3
 item1| Transfer Item  |   0    |    3    |  4

My desired output

 item |Transaction Type| Qty_IN | Qty Out | End Bal
------|----------------|--------|---------|-------
 item1| Beg. Balance   |   0    |    0    |  15
 item1| Received Item  |   5    |    0    |  20
 item1| Transfer Item  |   0    |    2    |  18
 item1| Transfer Item  |   0    |    3    |  15

Is there another way to get my desired output without using cursor?

odlan yer
  • 721
  • 1
  • 7
  • 15
  • Possible duplicate of [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) – j4rey Jul 20 '17 at 05:39
  • I think the last two row of your *End Bal* of your **desired output** is should actually be `18` and `15`. – j4rey Jul 20 '17 at 05:42

1 Answers1

1

One standard way of obtaining a running balance is to use a correlated subquery in the select statement which computes the running sum of the difference between the in and out amounts:

SELECT *,
    (SELECT SUM(t2.Qty_IN - t2.Qty_OUT) FROM yourTable t2
     WHERE t2.[Transaction Sequence] <= t1.[Transaction Sequence]) [End Bal]
FROM yourTable t1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360