Reporting using Database and Spreadsheet

[Wm]

Sun, 7 Dec 2014 18:33:27
<000001d01243$ebfed680$c3fc8380$@duvallet.serv0>  Pierre DUVALLET
<p.duvallet.serv0 at orange.fr>

>Hello,
>
>In response to topic N°4, this concerns pivot table.
>
>Following are the concerned SQL Queries. I could not tell you if they are
>backend independent as I only use MySql.
>
>I also have written a document to explain the complete configuration (using
>Excel). I can send this doc but it is about a 1Mo PDF doc.
>
>
>
>**********************
>Revenus.Qry (Income through month and years)
>SELECT transactions_0.description, accounts_1.name, Value_num/Value_denom AS
>'Montant', month(post_date), year(post_date)
>FROM gnucash.accounts accounts_0, gnucash.accounts accounts_1,
>gnucash.accounts accounts_2, gnucash.splits splits_0, gnucash.transactions
>transactions_0
>WHERE splits_0.tx_guid = transactions_0.guid AND splits_0.account_guid =
>accounts_0.guid AND accounts_0.parent_guid = accounts_1.guid AND
>accounts_1.parent_guid = accounts_2.guid AND
>((accounts_0.account_type='INCOME'))
>ORDER BY year(post_date), month(post_date), accounts_0.name,
>accounts_0.description
>**********************

-- sqlite3
-- dropped gnucash. from tables as my db is called something different
-- adjusted date functions for MySQL v sqlite
-- changed displayed account name to accounts_0 as accounts_1 just shows
INCOME here
-- how many account levels do you have, Pierre ?

Select
  transactions_0.description,
  accounts_0.name, -- was accounts_1
  splits_0.value_num / splits_0.value_denom As 'Month',
  SubStr(transactions_0.post_date, 5, 2) * 1 As M,
  SubStr(transactions_0.post_date, 1, 4) * 1 As Y
From
  accounts accounts_0,
  accounts accounts_1,
  accounts accounts_2,
  splits splits_0,
  transactions transactions_0
Where
  splits_0.tx_guid = transactions_0.guid And
  splits_0.account_guid = accounts_0.guid And
  accounts_0.parent_guid = accounts_1.guid And
  accounts_1.parent_guid = accounts_2.guid And
  (accounts_0.account_type = 'INCOME')
Order By
  SubStr(transactions_0.post_date, 1, 4),
  SubStr(transactions_0.post_date, 5, 2),
  accounts_0.name,
  accounts_0.description


>Expenses (Expenses through month and Years)
>SELECT accounts_1.name, transactions_0.description, splits_0.memo,
>Value_num/Value_denom AS 'Montant', month(post_date), year(post_date)
>FROM gnucash.accounts accounts_0, gnucash.accounts accounts_1,
>gnucash.accounts accounts_2, gnucash.splits splits_0, gnucash.transactions
>transactions_0
>WHERE splits_0.tx_guid = transactions_0.guid AND splits_0.account_guid =
>accounts_0.guid AND accounts_0.parent_guid = accounts_1.guid AND
>accounts_1.parent_guid = accounts_2.guid AND
>((accounts_0.account_type='EXPENSE'))
>ORDER BY year(post_date), month(post_date), accounts_0.name,
>accounts_0.description

same problem as above with accounts.name, you are presuming a certain
depth and consistency in account structure

rather than go through the other scripts do my comments above make
sense, P ?

-- 
Wm...