Re: 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