Thread: How to distribute quantity if same product is in multiple rows
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)
(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
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>. 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
(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).