Re: How to distribute quantity if same product is in multiple rows - Mailing list pgsql-general

From Andrus
Subject Re: How to distribute quantity if same product is in multiple rows
Date
Msg-id D49AD4D62A434D6584E314A7E1B31CA9@andrusnotebook
Whole thread Raw
In response to How to distribute quantity if same product is in multiple rows  ("Andrus" <kobruleht2@hot.ee>)
List pgsql-general
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.


pgsql-general by date:

Previous
From: Ben Chobot
Date:
Subject: Re: Getting statistics for each sql statement?
Next
From: John Gage
Date:
Subject: Re: Clarification of the "simple" dictionary