Calculate data based on previous values of rows [duplicate]

Question

I have information in base tables which is like this.

ORDER#	Date	Item	Qty	Onhand	Avl Fill
100030505	2024-11-19	862181010	84.00000	164	80
100030505	2024-11-20	862181010	85.00000	164	-5
100030505	2024-11-21	862181010	86.00000	164	-91
100030505	2024-11-22	862181010	87.00000	164	- 178

I need to calculate a column based on this that would show line by line Onhand-Qty based on previous line. For example: 1st row would give as (164-84) = 80 2nd row would give me 80-85 = -5 and so on. It is based on item and date. If item would change, then the counter should restart.

How can I achieve this in Oracle SQL?

Need to calculate Avail_to_fill in this.

Answer 1

You can use analytic functions to solve this problem. Here's the scripts:

create table tmp(
    ord INT,
    dt DATE,
    itm INT,
    qty DOUBLE PRECISION,
    onhand INT
);

insert into tmp values(100030505, DATE '2024-11-19', 862181010, 84.00000, 164);
insert into tmp values(100030505, DATE '2024-11-20', 862181010, 85.00000, 164);
insert into tmp values(100030505, DATE '2024-11-21', 862181010, 86.00000, 164);
insert into tmp values(100030505, DATE '2024-11-22', 862181010, 87.00000, 164);


select ord, dt, itm, qty, onhand,
        onhand - SUM(qty) OVER (
        PARTITION BY ord, itm 
        ORDER BY dt 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS AvlFill
from tmp;

The result is as below：