Re: question about query

El 30/06/11 15:44, David Johnston escribió:
> ----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Linos
> Sent: Thursday, June 30, 2011 9:00 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] question about query
>
> Hi,
>     i am trying to obtain from the database what we have been doing in a
> excel sheet from some time.
>
> I have some tables where i store the documents associated with our
> customers, invoices, payments and others, what i need to get from this
> tables it is a movement log where i can get what payment it is pending from
> this customer after the change in this row, for example:
>
> movement              |   qty      |   pending
> invoice N-111         | 1000.0 |   1000.0
> payment 1 N-111   |   200.0 |     800.0
> payment 1 N-111   |   150.0 |     550.0
> invoice N-112         |   350.0 |     900.0
>
> how could i calculate this pending column that does not exists in the
> database and i need to be based on last result row? I would like to get with
> raw SQL but the need to use any procedural language would not be a problem.
>
>>>>>>>>>>>>>>>>>>>>
>
> I would create an "Accounts Receivable" (A/R) relation (view probably but
> you could create a table as well) that is basically a UNION between the
> invoice and payment tables.  This will get you the "qty" column (but make
> sure you use positive and negative numbers).
>
> I would then create a table returning function that will calculate and
> output the running total "pending".  This calculation may possibly be done
> using a Window function but if not you can query the A/R relation and step
> over each result record in order to calculate the running total.
>
> David J.
>
>

Thanks for pointing me this David, with the idea and the help from

http://www.postgresonline.com/journal/index.php?/archives/119-Running-totals-and-sums-using-PostgreSQL-8.4-Windowing-function.html

article i think i get it.

Miguel Angel.