Add values of starting row and next row based on a colum from other table

Question

I have two tables.

TABLE A

Product	Plant	Stock
11248	ABC12	65
98490	GHI34	54
11248	DEF56	25

TABLE B

Product	Plant	Week	Sales	Production
11248	ABC12	0	7894	38410
11248	ABC12	...	...	...
11248	ABC12	11	6887	84351

First, I would like to add the only value of stock from TABLE A in the week 0 like the table below

Product	Plant	Week	Sales	Production	Stock
11248	ABC12	0	7894	38410	65

Second, calculate the stock from the rest of the week using the operation (Production+Stock)-Sales and get a table like this.

Product	Plant	Week	Sales	Production	Stock
11248	ABC12	0	7894	38410	65
11248	ABC12	1	6587	87427	(87427+65)-6587= 80905
11248	ABC12	...	...	...	...
11248	ABC12	11	6887	84351	(84351+Stock_row10)-6887=NewStock

I try two query's

The first query:

with Table_A1 (Product,  Plant, Stock) as (select Product,  Plant, Stock from TABLE_A)
          select Product, 
                Plant, 
                Week, 
                Sales, 
                Production, 
          from TABLE_B;
case 
  when Table_A1.Product = TABLE_B.Product and w.Plant=TABLE_B.Plant and TABLE_B.Week=0 then TABLE_A.Stock
  when Table_A1.Product = TABLE_B.Product and w.Plant=TABLE_B.Plant and TABLE_B.Week=1 then (TABLE_B.Production+TABLE_A.Stock)-TABLE_B.Sales
end ;

But I got the error "ERROR: Syntax error at or near 'from' "

The second query:

ALTER TABLE TABLE_B
ADD Stock INT;

SELECT * from TABLE_B
inner join TABLE_A ON TABLE_A.Product=TABLE_B.Product
case 
when Table_A1.Product = TABLE_B.Product and w.Plant=TABLE_B.Plant and TABLE_B.Week=0 then TABLE_A.Stock
end;

But I got the error "ERROR: Syntax error at or near 'CASE' ". Any help?

Answer 1

Use a window function for this.

Your use case is complicated by the fact that your desired results show the initial stock is the stock at the first week's end. I have seen that before, and I carry forward the first week's sales and production to adjust for their exclusion from later stock calculations:

with nets as (
  select a.product, a.plant, b.week, 
         b.sales, b.production, 
         sum(b.sales) over w as running_sales,
         sum(b.production) over w as running_production,
         first_value(b.sales) over w as first_week_sales,
         first_value(b.production) over w as first_week_production,
         a.stock as first_week_stock
    from table_a a
         join table_b b on (b.product, b.plant) = (a.product, a.plant)
  window w as (partition by a.product, a.plant order by b.week)
)
select product, plant, week, sales, production, 
       running_production + first_week_stock - first_week_production
       - running_sales + first_week_sales as stock
  from nets;

Working fiddle.