Search Postgresql Archives

Re: question about query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



El 30/06/11 15:44, David Johnston escribió:
----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Linos
Sent: Thursday, June 30, 2011 9:00 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject:  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.


i have created the union to get all the records (giving payments negative sign) but what i don't know how to do it is use a window function to create the column with the running total, any short example of syntax please?

Miguel Angel.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux