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

(anonymous) wrote:

>>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.

> If four pieces arrived, first position of 2 pieces should
> marked as delivered.
> Second position of 4 pieces shoudl be marked as partialli
> delivered by setting undelivered quantity
> of this row to 2

> How to use your suggestion for this ?

Que? You take the query above, join it in the "UPDATE" and
set the delivered quantity to the minimum of the ordered
quantity and "taitmkogus - sumkogus".

> How to implement this is PostgreSql 8.1,8.2, 8.3 ?
> [...]

An example for calculating running sums without window
functions can be found at
<URI:http://archives.postgresql.org/pgsql-sql/2001-07/msg00152.php>.
I would rather use a PL/pgSQL function in this case, though.

Tim