SQL: a consolidated balance sheet

[Wm]

Sun, 28 Dec 2014 10:30:25 <rstfmEQBv9nUFwkM at tarrcity.demon.co.uk>  Wm
<wm+gnc at tarrcity.demon.co.uk>

[ff to self, ongoing SQL tutorial of sorts]

>this is a gnucash balance sheet that should work with any SQL backend

>===
>Select
>  Sum(splits.value_num),
>  accounts.account_type
>From
>  splits Inner Join
>  accounts On accounts.guid = splits.account_guid
>Group By
>  accounts.account_type
>Having
>  accounts.account_type Not In ('EXPENSE', 'INCOME')
>Order By
>  accounts.account_type
>===
>
>that, people, is a balance sheet, the rest is presentation.

So far my SQL examples haven't offered anything you can't already get
from gnc so, you may ask yourself, why bother ?

Well, to pique your interest here is something you can't do with gnc, a
consolidated balance sheet.  I've always thought this should work and
actually got around to trying it out this morning.  It fell together by
itself.

===
ATTACH DATABASE 'A.sqlite' AS 'A';
ATTACH DATABASE 'B.sqlite' AS 'B';

SELECT Sum(A.splits.value_num) V
        ,A.accounts.account_type T
        ,A.accounts.NAME N
FROM A.splits
INNER JOIN A.accounts ON A.accounts.guid = A.splits.account_guid
GROUP BY A.accounts.account_type
HAVING A.accounts.account_type NOT IN ('EXPENSE', 'INCOME')

UNION ALL

SELECT Sum(B.splits.value_num) V
        ,B.accounts.account_type T
        ,B.accounts.NAME N
FROM B.splits
INNER JOIN B.accounts ON B.accounts.guid = B.splits.account_guid
GROUP BY B.accounts.account_type
HAVING B.accounts.account_type NOT IN ('EXPENSE', 'INCOME')

ORDER BY T, N;
===

Note: out of the 3 gnc SQL backends only sqlite supports ATTACH.  For
postgres one might use dblink and for mysql it looks like you can just
query files from different db's by name so don't need the ATTACH at all.
I'm not too worried about the apparent inconsistency at this stage
because my sample use thoughts seem to mitigate against using separate
postgres or mysql dbs simply from an overkill POV.

---------

A bit of explanation about the code:

the first two lines ATTACH a DATABASE.  The DATABASEs A.sqlite and
B.sqlite are complete gnc books saved using the sqlite backend.

the SELECT bit does the balance sheet query quoted up top on *both* of
the gnc books and presents the combined results.

-------------

This gives us a consolidated balance sheet (other reports can be
consolidated too BTW).

-------------

What is this consolidation thing and how might it be useful to me?


A simple example may be Amy and Bez who co-habit / flat share /
whatever.  They each keep their own gnc book and occasionally want to
mash things together to get an overview.

Corporate consolidation works in a similar way, IeatCake Ltd and
ImakeCake Ltd operate as independent entities but IownCake plc
reasonably wants to report on the companies it owns and see them
reported together.

And finally, for now, charitable / NFP Restricted Funds.  Easy, you
create a new book for each fund, utilise it as normal using Expenses,
contribute to the fund using either Income or additions to Equity as you
see fit.

The neat bit is that gnc's ordinary reports all work for the discrete
person / company / fund where that level of accounting is required. What
SQL does is allow you to put things together, to obtain an overview and
report on that.

P.S. in case anyone is wondering I can't see why a consolidated virtual
book couldn't be created and reported on, no writing allowed.  Next
time, maybe.

-- 
Wm...