How to distribute quantity if same product is in multiple rows
От | Andrus |
---|---|
Тема | How to distribute quantity if same product is in multiple rows |
Дата | |
Msg-id | 62802AE8073A40839826D9D0E84690F7@andrusnotebook обсуждение исходный текст |
Список | pgsql-general |
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)
В списке pgsql-general по дате отправления: