Thread: 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.
Thanks!
Regards,
Miguel Angel.
On 30/06/2011 9:00 PM, Linos wrote: > 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. > Without knowing how you've structured your data in the database, that's pretty much impossible to answer. What are your table definitions? What does your raw data currently look like? Show some example rows. -- Craig Ringer
sorry, i have more searching for a way to do something like this but i should
have posted the tables anyway, i have any more documents but all have a
structure like this:
invoices:
CREATE TABLE factura
(
id_factura serial NOT NULL, -- invoice id
id_cliente integer NOT NULL, -- customer relation
fecha date NOT NULL, -- date of invoice
importe numeric(10,2) NOT NULL, --total invoice qty
time_stamp_creacion timestamp without time zone NOT NULL DEFAULT now()
CONSTRAINT factura_cabecera_pkey PRIMARY KEY (id_factura)
) WITH (
OIDS=FALSE
);
payments:
CREATE TABLE factura_fecha_cobro
(
id_factura integer NOT NULL, --invoice id
id_cobro integer NOT NULL, --number of payment for this invoice
fecha date NOT NULL, --date of payment
importe numeric(10,2) NOT NULL, --qty paid
time_stamp_creacion timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT factura_fecha_cobro_pkey PRIMARY KEY (id_factura, id_cobro),
CONSTRAINT factura_fecha_cobro_id_factura_fkey FOREIGN KEY (id_factura)
REFERENCES factura (id_factura) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
Regards,
Miguel Angel.
El 30/06/11 15:11, Craig Ringer escribió:
> On 30/06/2011 9:00 PM, Linos wrote:
>> 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.
>>
> Without knowing how you've structured your data in the database, that's pretty
> much impossible to answer.
>
> What are your table definitions?
>
> What does your raw data currently look like? Show some example rows.
>
> --
> Craig Ringer
----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.
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. > 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.
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.
-----Original Message----- 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? >>>>>>>>>> You'll need to check the documentation for the exact syntax but the following should work as desired. SELECT description, amount, transaction_date, SUM(amount) OVER (ORDER BY transaction_date, amount) FROM accountsreceivable You do NOT use a "GROUP BY" in this instance because the "OVER" clause following the SUM function turns it into a Window function which aggregates in the context of a virtual window. Using ORDER BY causes the SUM() function to behave like a Running Sum. You have not specified your version and Window functions are only available in newer releases (>=8.4 I believe). If you cannot use this then you will have to write a store procedure. David J.