I have a data set like below,
Lot Size Reported QTY Qty Balance
150 100
150 100
150 80
150 80
150 5
The Qty Balance needs to calculated as follows,
Row 1 = Lot Size - Reported Qty (row1) => 150-100 = 50
Row 2 = Reported Qty (row1) - Reported Qty(row2) => 100-100 =0
Row 3 = Reported Qty (row2) - Reported Qty(row3) => 100-80 =20
... till the last row
My expected result is
Lot Size Reported QTY Qty Balance
150 100 50
150 100 0
150 80 20
150 80 0
150 5 75
How do I achieve this in a query?