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