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

How to distribute quantity if same product is in multiple rows

From
"Andrus"
Date:
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)


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

From
Tim Landscheidt
Date:
(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).



Attachment

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

From
"Andrus"
Date:
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.


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

From
Tim Landscheidt
Date:
(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

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

From
"Andrus Moor"
Date:
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

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

From
"Tim Landscheidt"
Date:
(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).