Order contains same product in multiple rows.
I tried to calculate undelivered quantity using script below but it produces
wrong result:
delivered quantity is substracted from both rows, not distributed.
How to distibute undelivered quantity according to row quantity in every row
?
Can it be done using SQL or should SCAN loop in plpgsql used?
Andrus.
-- Order details
CREATE TEMP TABLE rid (
id serial primary key,
toode char(20), -- product id
kogus numeric(12,5), -- ordered quantity
taitmata numeric(12,5) ) -- undelivered quantity which needs calculated
on commit drop;
insert into rid (toode,kogus) values ('PRODUCT1', 10 );
insert into rid (toode,kogus) values ('PRODUCT1', 20 );
-- Delivered quantities
CREATE TEMP TABLE mtait (
toode char(20) primary key, -- product id
taitmkogus numeric(12,5) ) -- delivered quantity
on commit drop;
insert into mtait(toode, taitmkogus) values ('PRODUCT1', 15);
UPDATE rid SET taitmata= rid.kogus -
COALESCE((SELECT taitmkogus FROM mtait WHERE mtait.toode=rid.toode),0);
select taitmata
from rid;
Observed:
-5
5
Expected:
0
15
First row 10 is fully delivered since 10<15. From second row quantity 20
only 5 is delivered (15-10=5)
