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

[Wm]

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

>
>Part 1 of a possibly strange tutorial
>
>this is a gnucash balance sheet that should work with any SQL backend
>
>===
>select sum(value_num) from splits
>===
>
>the result should be 0
>
>that is how double entry book keeping works, we want our accounts to
>balance, some people might view that as a trial balance but it doesn't
>matter right now so long as it all comes to nothing.
>
>Part 2 aka that isn't what I was expecting
>
>OK, so you've some accounting knowledge and already know that Income and
>Expenditure shouldn't be part of the balance sheet except in summary
>
>No problem, we do need another table though, the gnucash people decided
>to call the place where the details of accounts are recorded accounts
>(splits isn't so obvious but makes sense later on) so
>
>===
>Select
>  Sum(splits.value_num),
>  accounts.name
>From
>  splits Inner Join
>  accounts On accounts.guid = splits.account_guid
>Group By
>  accounts.name
>===
>
>which will give you a balance for each of your accounts and the name of
>the account next to it, we still haven't got our balance sheet, so
>
>let's add account_type and group by that
>
>===
>Select
>  Sum(splits.value_num),
>  accounts.name,
>  accounts.account_type
>From
>  splits Inner Join
>  accounts On accounts.guid = splits.account_guid
>Group By
>  accounts.name, accounts.account_type
>===
>
>the fun bit (one of many) is that we don't need the account name, just
>the type, so
>
>===
>Select
>  Sum(splits.value_num),
>  accounts.account_type
>From
>  splits Inner Join
>  accounts On accounts.guid = splits.account_guid
>Group By
>  accounts.name, accounts.account_type
>===
>
>now we order the accounts by type
>
>===
>Select
>  Sum(splits.value_num),
>  accounts.account_type
>From
>  splits Inner Join
>  accounts On accounts.guid = splits.account_guid
>Group By
>  accounts.account_type
>Order By
>  accounts.account_type
>===
>
>Noooooooooooo! I hear you call, it isn't a balance sheet yet.
>
>Correct, we need to get rid of EXPENSE and INCOME accounts, try this
>
>===
>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.
>
>If you are interested in this sort of stuff, let me know.  If you
>already know it, say so, let's share stuff.
>
>If you're a gnc senior and find it irritating speak your word.
>
>My interest is that it takes a lot less SQL than the current reporting
>methods to produce a balance sheet.
>
>Next time I'll visit the p&l, income statement, whatever in sql, first I
>want this sort of report shot at and possibly killed.
>

balance sheets are a view at a point in time.  Our last balance sheet
didn't specify a point in time because it didn't need to, it was a view
of our entire accounts and included all past and future transactions as
of now.

an overview.

that may take old hands a moment to adjust to, imagine drawing up a
balance sheet at some point in the future knowing everything you know is
already going to happen.  that is what we had.

more usually a balance sheet is wanted for a particular point in time
like the end of last month or the end of a financial year or now.  we
need to know the dates of transactions to do that so we know whether to
include them in our balance sheet or not.

gnc stores transaction details in a table called (you're ahead of me,
aren't you?) transactions.  each split belongs to a transaction which
has a date so we just join them up like so.

note: you don't have to run this query as is, it is intermediate, if you
want to because you're curious expect it to produce a load of stuff, I'm
showing it because I'd like you to understand how I get to the next bit

===
Select
  Sum(splits.value_num),
  accounts.account_type,
  transactions.post_date
From
  splits Inner Join
  accounts On accounts.guid = splits.account_guid Inner Join
  transactions On splits.tx_guid = transactions.guid
Group By
  accounts.account_type, transactions.post_date
Having
  accounts.account_type Not In ('EXPENSE', 'INCOME')
Order By
  accounts.account_type
===

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]

[1] minor point, the different SQL backends do dates differently, I'm
using SQLite3 above as that is the one most people will likely be using
in future, if this is your first SQL experience don't worry about the
date details, the chances are your chosen tool will do it for you

anyhow

people familiar with accounting should be starting to feel they're on
familiar territory now.  we've got balance sheets for the start and end
of our reporting period, all we need now are the transactions in
between.

aside: the Income Statement and Profit and Loss report are exactly the
same report in gnc, folks, so pull your horns in.  it is just a name for
a report of what happened between two dates, you can call it what you
want where you live, even "beans put into and taken out of the jar", I
really don't care

onwards

the first clue as to how to achieve this was right at the top, our SQL
double entry equation

===
select sum(value_num) from splits
===

to make the balance sheet we did this

===
Having
  accounts.account_type Not In ('EXPENSE', 'INCOME')
===

what do you expect we're going to do to get the bit in between?

yup, we're going to use the other accounts and specify a period

===
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' And
  transactions.post_date >= '2013-01-01'
Group By
  accounts.account_type
Having
  accounts.account_type In ('EXPENSE', 'INCOME')
Order By
  accounts.account_type
===

that's it, folks! your accounting cycle in SQL :)

I've received one bit of private fan mail but if you're wondering about
anything, please ask.

-- 
Wm...