Re: [GENERAL] Average Balance life
Hi, On Tuesday, October 30, 2012 8:12:25 PM UTC+1, David Johnston wrote: Start learning about Window functions/clauses: http://www.postgresql.org/docs/9.2/interactive/tutorial-window.html The lag function over a window ordered by date will allow you to calculate how many days since the last transaction. Will do, thanks for the tip. You sample data is simplistic to the point of being unusable. (...) But given that most accounts have numerous debits and credits flowing through them the logic by which you choose the endpoints is unclear but fundamental to the solution you seek. At first blush you seem to need to decide whether you want to deal with FIFO, LIFO, or specific-lots. I am trying to find accounting errors in the style of: credits/debits put into wrong accounts (ie: payments to providers without bills, etc), payrolls with typing errors... So if I know, for example, that all payroll accounts go to 0 two days after getting credited (it's Average Balance Life would be 2 days) I want to see which accounts do not comply ie: part or the whole balance is not debited on the two days. Reading the LAG function it seems it may help me to catch the most simple cases (ie: those with sequential credit / debit movements). Do you have some other identifier (i.e., control) attached to these amounts that would aid in choosing the endpoints? Nope. Anyway I just thought that ANOTHER way to look at it would be If account balance was going to 0 and now it is not without having reached 0, alert this seems like a far simpler approach that would detect the same kind of errors I'm looking for (I'd have to restart set the balance to 0 on every alert, etc). I'll work on that one which smells like some of this window function stuff. Thanks, marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Average Balance life
Hi there, Not sure if this is the right place to post this question: I'm tryting to find a way to do the following without going row by row on the client program doing the calculations: I've got a movements table (from an accounting program) for which I've already done monthly balances and running balances but now I need to calculate the life of the balances. Ie, account 1 is 0 and gets 1000 on day 1, -1000 on day 3. That would make a life of two days for this balance. Note this is completely diferent to average balance. In words that would be Every time money goes into this account it takes an average of X days to go out. Any ideas on how could I do this calculation inside PostgreSQL to avoid transfering all rows to the client? In case you are curious, I want to do this calculation to see which accounts have balance lifes to long in order to find accounting errors. Thanks, marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Average Balance life
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of teleni...@gmail.com Sent: Tuesday, October 30, 2012 7:34 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Average Balance life Hi there, Not sure if this is the right place to post this question: I'm tryting to find a way to do the following without going row by row on the client program doing the calculations: I've got a movements table (from an accounting program) for which I've already done monthly balances and running balances but now I need to calculate the life of the balances. Ie, account 1 is 0 and gets 1000 on day 1, -1000 on day 3. That would make a life of two days for this balance. Note this is completely diferent to average balance. In words that would be Every time money goes into this account it takes an average of X days to go out. Any ideas on how could I do this calculation inside PostgreSQL to avoid transfering all rows to the client? In case you are curious, I want to do this calculation to see which accounts have balance lifes to long in order to find accounting errors. Thanks, marc Start learning about Window functions/clauses: http://www.postgresql.org/docs/9.2/interactive/tutorial-window.html The lag function over a window ordered by date will allow you to calculate how many days since the last transaction. You sample data is simplistic to the point of being unusable. Balance Life may have a concrete definition in your domain but the example and descriptions are lacking. If indeed you mean days between transactions then a direct window function application will solve the problem easily. But given that most accounts have numerous debits and credits flowing through them the logic by which you choose the endpoints is unclear but fundamental to the solution you seek. At first blush you seem to need to decide whether you want to deal with FIFO, LIFO, or specific-lots. Do you have some other identifier (i.e., control) attached to these amounts that would aid in choosing the endpoints? Regardless, most likely any pure SQL solution is going to require the use of Window functions so at least learn what those can do and then if you still need help reply back with more details. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
