Re: How to distribute quantity if same product is in multiple rows

(anonymous) wrote:

> 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?
> [...]

It can be done in SQL: "SUM(kogus) OVER (PARTITION BY toode
ORDER BY ID) - kogus" (*1) will give you the running sum of
the product up to that row. You can then subtract that value
from the delivered quantity to calculate the delivered quan-
tity for the current row.

But doing so automatically is probably bad. For example,
if a user has a purchase order with one position of two
pieces and one position of four, it is very likely that when
a shipment of four pieces arrives, the latter position shall
be marked as delivered. So I would leave the decision to the
user.

Tim

(*1) In PostgreSQL 9.0, you might be able to use "ROWS
BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING" instead
(untested).