I would like to build an sql report that will show the practice's debtors situation. ie as at dd/mm/yy total outstanding=$$$$, outstanding 30 days=$$$$, 60 days=$$$$.

The total outstanding is relatively easy - sum the opening balances for that date (assuming that the specified date is a month end and the statement run has been done for that date).

However I am unsure how to get an 'outstanding for nn days' figure.  One possible approach is to look at the total-allocated figures for each invoice, counter sale and credit in a date range between date D and D less 30 days. This will give the total outstanding for between 0 and 30 days prior to date D.

However, that the problem here is that to get a number for 'outstanding for more than nnn days' we really have to scan all transactions older than nnn days prior to our as-at date.  UNLESS it is possible to write sql that says 'find the opening balance prior to a date nnn days ago and sum that and the total-allocated transactions from that opening balance date forward.

Does anyone have any other ideas?

Note that I had a quick look at the source and I can see the code selecting OVERDUE_INDEX and NON_OVERDUE_INDEX but I cannot figure out where it builds the actual query.

Regards, Tim G