SQL - LAG Function - Running Total

Question

I hope someone can please help. I don't know if this is possible but here goes.

Using SQL Developer connected to an Oracle database.

I have the following Data Set being returned from a query

Item  Qty  On Hand
A     6     88
A     6     88
A     6     88
B     2      4
B     1      4
B     2      4

Using the LAG function for field Shortage

Lag(Qty) Over (Partition By Item Order By Item) As Shortage

Returns

Item  Qty  On_Hand   Shortage
----  ---  -------   ---------
A     6     88       NULL
A     6     88       6
A     6     88       6
B     2      4       NULL
B     1      4       1
B     2      4       2

I Solve the NULL value by using

Nvl(Lag(Qty) Over (Partition By Item Order By Item), On_Hand + Qty) As Shortage

(If The 'LAG' Value is Null replace the NULL with the Current On_Hand - Qty)

Item  Qty  On_Hand   Shortage                                 
A     6     88        82
A     6     88         6
A     6     88         6
B     2      4         2
B     1      4         2
B     2      4         1

That is fine for the first row. What I am really looking for is

Item  Qty  On_Hand   Shortage  Shortage2
                               (Funtion Below)  
A     6     88        82
A     6     88        76
A     6     88        70
B     2      4         2
B     1      4         1
B     2      4        -1

where the Shortage column is effectively a calculation of

The Previous Row Shortage Value - Current Row Quantity Required.

Is this possible ?

I do hope so.

Many thanks in advance to anyone for your assistance

Regards

Iain

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-10-03T11:17:56

I think you want a cumulative sum. However, you need a column that specifies the ordering of the rows, and no such column is obvious in your data:

select t.*,
       (on_hand - sum(qty) over (partition by item order by ?)) as shortage
from t;

You can use item but the results are not stable -- that is, you can run the same code twice and get different results.

SQL - LAG Function - Running Total

1 Answers