Thread: complicated running aggregate

I am trying to build a query with a running total, but it is a bit
complicated.
We have different stock for leaded and unleaded parts, but we also have
one stock if it is lead free and can be used in a leaded process (lfb -
lead free both).
I have the following fields: id,fkey_id,partid, duedate, qty, leadfree,
stock_l, stock_lf, stock_lfb

I want to write a query that tells me for each row how much leaded stock
I have already committed and how much lead free stock I have committed.
If we use stock that is LFB then it goes on the commitment for both
columns. If it is a lead free row and we do not have enough stock, it is
only a commitment for the lead free stock (same idea for the leaded rows).
The rule is that if you have leaded or lead free stock, you use that
before LFB stock.
The table can be self joined on partid where the second table has an
earlier duedate (and an earlier fkey_id if the dates are the same)

I have rows:
id    fkey_id    partid    duedate        qty    leadfree  stock_l
stock_lf    stock_lfb
1    100            126        1/1/2011    100    t                10
        15            300
2    105            126        1/2/2011    150    t                10
        15            300
3    109            126        1/3/2011    200    f                10
        15            300
4    110            126        1/4/2011    100    t                10
        20            300
5    115            126        1/5/2011    200    f                10
        20            300
6    114            126        1/6/2011    500    t                10
        20            300

My end result should look like:

id    fkey_id    partid    duedate        qty    leadfree  stock_l
stock_lf    stock_lfb    commited_L    commited_LF
1    100            126        1/1/2011    100    t                10
        15            300            0                        0
2    105            126        1/2/2011    150    t                10
        15            300            85                      100
3    109            126        1/3/2011    200    f                10
        15            300            235                    250
4    110            126        1/4/2011    100    t                10
        20            300            435                    315
5    115            126        1/5/2011    200    f                10
        20            300            435                    415
6    114            126        1/6/2011    500    t                10
        20            300            635                    415


Currently I'm generating this information using a plpsql loop, but I
would like to change it to an SQL query, if possible.


Thanks
Sim

Sim Zacks wrote:
> I am trying to build a query with a running total, but it is a bit
> complicated...Currently I'm generating this information using a plpsql loop, but I
> would like to change it to an SQL query, if possible.
>

It's hard to do a running total with the sort of requirements you have
using simple SQL.  If you have PostgreSQL 8.4, it's much much easier to
do that using the SQL Window functions:
http://www.postgresql.org/docs/current/static/tutorial-window.html

If you're on an earlier version, using a loop in a function is not a bad
approach.  Getting the query right without windowing is possible, but
painful enough that it may not be worth your trouble.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

That's what I was afraid of :-(
I'm currently on 8.2
We're planning on starting 9.0 testing very soon, so I may just leave it
until then.

Sim

On 6/24/2010 5:23 PM, Greg Smith wrote:
>
> If you're on an earlier version, using a loop in a function is not a
> bad approach.  Getting the query right without windowing is possible,
> but painful enough that it may not be worth your trouble.
>