Re: question about query
От | David Johnston |
---|---|
Тема | Re: question about query |
Дата | |
Msg-id | 003301cc372b$e26fa230$a74ee690$@yahoo.com обсуждение исходный текст |
Ответ на | question about query (Linos <info@linos.es>) |
Ответы |
Re: question about query
Re: question about query |
Список | pgsql-general |
----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.
В списке pgsql-general по дате отправления: