Running totals in second columns - follow up

Question

This is in follow-up to my previous question.

The data set I have now is like this

PART_NO  ORDER_NO   OPERATION_NO LOT_SIZE   REPORTED_QTY    QTY_BALANCE
305002   10374      10           3000       2417            
305002   10374      11           3000       2417            
305002   10374      12           3000       2417            
305002   10374      13           3000       2417            
305002   10374      14           3000       2417            
305002   10374      15           3000       2417            
305002   9145       10           2000       1950            
305002   9145       20           2000       1949            
305002   9145       30           2000       1949            
305002   9145       40           2000       1949            
305002   9145       50           2000       1949            
305002   9145       60           2000       1949

When i use the LAG function, I get the results like the below,

PART_NO  ORDER_NO   OPERATION_NO LOT_SIZE   REPORTED_QTY    QTY_BALANCE
305002   10374      10           3000       2417            583
305002   10374      11           3000       2417            0
305002   10374      12           3000       2417            0
305002   10374      13           3000       2417            0
305002   10374      14           3000       2417            0
305002   10374      15           3000       2417            0
305002   9145       10           2000       1950            467
305002   9145       20           2000       1949            1
305002   9145       30           2000       1949            0
305002   9145       40           2000       1949            0
305002   9145       50           2000       1949            0
305002   9145       60           2000       1949            0

The problem is, when the order_no changes, i need to start the LAG evaluation from the beginning for that order_no. The expected result set is

PART_NO  ORDER_NO   OPERATION_NO LOT_SIZE   REPORTED_QTY    QTY_BALANCE
305002   10374      10           3000       2417            583
305002   10374      11           3000       2417            0
305002   10374      12           3000       2417            0
305002   10374      13           3000       2417            0
305002   10374      14           3000       2417            0
305002   10374      15           3000       2417            0
305002   9145       10           2000       1950            50
305002   9145       20           2000       1949            1
305002   9145       30           2000       1949            0
305002   9145       40           2000       1949            0
305002   9145       50           2000       1949            0
305002   9145       60           2000       1949            0

I cant seem to get around this. How do make the LAG function understand that it needs to reset whenever the order_no changes??

score 0 · Accepted Answer · answered May 22 '15 at 16:57

Use PARTITION BY order_no clause inside the OVER declaration:

SELECT PART_NO, ORDER_NO, OPERATION_NO, Lot_Size,
       Reported_QTY,
       COALESCE( LAG( Reported_QTY ) OVER (PARTITION BY ORDER_NO ORDER BY null )
                - Reported_QTY, Lot_Size - Reported_QTY ) AS Qty_Balance
FROM   Lots
ORDER BY ORDER_NO DESC, OPERATION_NO

Demo: http://sqlfiddle.com/#!4/501c2/15

Running totals in second columns - follow up

1 Answers1