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
providerswithout 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
twodays.
Reading the LAG function it seems it may help me to catch the most simple cases (ie: those with sequential credit /
debitmovements).
> 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
withouthaving reached 0, alert" this seems like a far simpler approach that would detect the same kind of errors I'm
lookingfor (I'd have to restart set the balance to 0 on every alert, etc). I'll work on that one which smells like some
ofthis window function stuff.
Thanks,
marc
