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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
