SQL: what happened between two balance sheets aka the Income Statement or Profit + Loss a/c

Gregory Gincley rollenwiese at fastmail.net
Tue Dec 30 16:14:45 EST 2014


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256



On 12/29/2014 07:21 PM, Wm wrote:

[snipped]

> ===
> 
> what we actually want is all the transactions before some point in 
> time so let's make that part of our query and skip the actual date 
> in the output
> 
> === Select Sum(splits.value_num), accounts.account_type From
> splits Inner Join accounts On accounts.guid = splits.account_guid
> Inner Join transactions On splits.tx_guid = transactions.guid Where
>  transactions.post_date <= '2013-12-31' Group By 
> accounts.account_type Having accounts.account_type Not In 
> ('EXPENSE', 'INCOME') Order By accounts.account_type ===
> 
> this gives us a balance sheet at the end of 2013 and you can 
> probably work out how to get one for the end of 2012 yourself [1]
> 

This works sort of, but the market value of accounts tied to certain
commodities may not be accurate for any given effective date. In order
to caclulate the market value, you probably need to join to the prices
table and multiply the sum of the split quantity by the price for that
commodity as of your effective date. Or a suitable alternative if no
price is available for that exact date.

- -Greg
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAEBCAAGBQJUoxW9AAoJEODkk8hTyydzDQUIAKxglkEIl2BtYVXFjuADfssB
Nx5++UBGzmY6YEsu6aHSqUVrHweLDVS10ecTWb0jIAnUV6c8nqA7xK3aA5lJQORv
9NLDQZsPLnKDgYgHJgTkEWptJgY32Qx1305lGD9I2MzIhQOroE9crKygt/ACwopA
TN0FV+evs0Th6mMgBVJhw0Gz7De5XFAh8BDUFH5BSwPeE2HYPuFyovgyQ+l/aA/I
1Br9t7bIvwWGhqoojlWkbwSJKtrc1DBmb3hNlqh2s+wjKmpL/cQO5eh/X7dJhlpO
QPBqZZjbFYKCPHc3IX+1MPuVxtyBfmpSmnVLUd/WrHW03OYos9F8FMgOpCvSc68=
=Upxo
-----END PGP SIGNATURE-----


More information about the gnucash-user mailing list