About GnuCash 2.3.x / 2.4

[Colin Law]

2009/8/4 Phil Longstaff <plongstaff at rogers.com>:
> [snip]

>
> The balance for account 'ABC' on date 'D' can be determined by:
>
> SELECT SUM(amount_num),amount_denom FROM splits WHERE tx_guid IN (SELECT * FROM transactions WHERE post_date <= D) AND account_guid = (SELECT guid FROM accounts WHERE name = 'ABC') GROUP BY amount_denom

Just a note that this would fail if there were multiple accounts with
the same name (with different parents obviously).  It would be safer
to determine the account guid by an alternative route.

Colin

>
> This could *easily* not be the most efficient join, but it gives you the idea.  If you don't change the number of decimal digits for an account, I *think* that all splits for that account will have the same denominator, so the query will give you 1 row.  If you have changed the number of decimal digits, you might get more than 1 row, one for each denominator (100, 1000, 10000, ...) and will need to add them.  The SQL backend to gnucash actually uses:
>
> SELECT account_guid,reconcile_state,SUM(amount_num),amount_denom FROM splits GROUP BY account_guid,reconcile_state,amount_denom
>
> when it starts.  This generates 1 row per account_guid/reconcile_state/amount_denom triplet.  Scanning and addition gives 1 row per account_guid/reconcile_state pair, and these values provide the account balances.