By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE MAKE_IT_WORK
(
PRODUCT_KEY NUMBER,
WEEK NUMBER,
OPENING_STOCK NUMBER,
INTAKE NUMBER,
SALES NUMBER,
CLOSING_STOCK NUMBER,
FORWARD_COVER NUMBER
);
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK)
Values (1, 1);
1 rows affected
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK, INTAKE, SALES)
Values (1, 2, 1000, 80);
1 rows affected
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK, SALES)
Values (1, 3, 70);
1 rows affected
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK, SALES)
Values (1, 4, 90);
1 rows affected
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK, SALES)
Values (2, 1, 0);
1 rows affected
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK, INTAKE, SALES)
Values (2, 2, 6000, 500);
1 rows affected
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK, SALES)
Values (2, 3, 70);
1 rows affected
Insert into MAKE_IT_WORK (PRODUCT_KEY, WEEK, SALES)
Values (2, 4, 350);
1 rows affected
select product_key, week, opening_stock, intake, sales,
coalesce(opening_stock, 0)
+ sum(intake) over(partition by product_key order by week)
- sum(sales) over(partition by product_key order by week)
as closing_stock
from make_it_work
order by product_key, week;
| PRODUCT_KEY | WEEK | OPENING_STOCK | INTAKE | SALES | CLOSING_STOCK |
|---|---|---|---|---|---|
| 1 | 1 | null | null | null | null |
| 1 | 2 | null | 1000 | 80 | 920 |
| 1 | 3 | null | null | 70 | 850 |
| 1 | 4 | null | null | 90 | 760 |
| 2 | 1 | null | null | 0 | null |
| 2 | 2 | null | 6000 | 500 | 5500 |
| 2 | 3 | null | null | 70 | 5430 |
| 2 | 4 | null | null | 350 | 5080 |