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

Tim,

Thank you.

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 ?
How to implement this is PostgreSql 8.1,8.2, 8.3 ?

Andrus.

(*1) In PostgreSQL 9.0, you might be able to use "ROWS

BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING" instead
(untested).

PS. If possible please use cc: with my email address in reply.

(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&gt;.
I would rather use a PL/pgSQL function in this case, though.

Tim

Tim,

Thank you.

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.

I tried to get list of undelivered items using script below.
Second row value (22) is incorrect (it seems to be is cumulative sum but
must be undelivered quantity for this row).
How to fix this so that every row contains correct undelivered quantity ?

Andrus.

-- Order details
CREATE TEMP TABLE tellrid (
id serial primary key,
toode char(20), -- product id
kogus numeric(12,5) ) -- ordered quantity
on commit drop;

insert into tellrid (toode,kogus) values ('PRODUCT1', 10 );
insert into tellrid (toode,kogus) values ('PRODUCT1', 20 );

-- Delivery details
CREATE TEMP TABLE rid (
id serial primary key,
toode char(20), -- product id
kogus numeric(12,5) ) -- delivered quantity
on commit drop;

insert into rid (toode,kogus) values ('PRODUCT1', 8 );

select
tellrid.id,
max(tellrid.kogus) as ordered,

GREATEST( 0,

SUM(MAX(tellrid.kogus) )
OVER (PARTITION BY MAX(tellrid.toode) ORDER BY tellrid.ID)

- COALESCE( SUM(rid.kogus),0)

) as not_delivered
from tellrid
LEFT JOIN rid USING (toode)
GROUP BY 1

Observed:

id ordered not_delivered
1 10.00000 2.00000
2 20.00000 22.00000

Expected:

id ordered not_delivered
1 10.00000 2.00000
2 20.00000 20.00000