----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.
