Reporting using Database and Spreadsheet

[Pierre DUVALLET]

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

 

**********************

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

 

**********************

Expenses Versus Income

SELECT accounts_0.account_type, Value_num/Value_denom AS 'Montant',
month(post_date), year(post_date)

FROM gnucash.accounts accounts_0, 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.account_type='EXPENSE') OR
(accounts_0.account_type='INCOME'))

ORDER BY month(post_date), year(post_date), accounts_0.account_type

 

**********************

Accounts Balance

SELECT accounts_0.description, splits_0.value_num, accounts_0.name,
splits_0.Value_num/splits_0.Value_denom AS 'Montant', month(post_date),
year(post_date)

FROM gnucash.accounts accounts_0, 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.account_type='RECEIVABLE') AND
(splits_0.value_num<>0) OR (accounts_0.account_type='BANK') AND
(splits_0.value_num<>0) OR (accounts_0.account_type='MUTUAL') AND
(splits_0.value_num<>0) OR (accounts_0.account_type='CREDIT') AND
(splits_0.value_num<>0) OR (accounts_0.account_type='CASH') AND
(splits_0.value_num<>0))

ORDER BY accounts_0.description, accounts_0.name, year(post_date),
month(post_date)

 

**********************

Comptes Titres (Stock Accounts through month and years)

 

SELECT accounts_1.name, accounts_0.name,
splits_0.value_num/splits_0.value_denom AS 'Montant',
splits_0.quantity_num/splits_0.quantity_denom AS 'Quantite',
prices_0.value_num/prices_0.value_denom AS 'Valeur', year(date),
month(date),
(splits_0.quantity_num/splits_0.quantity_denom)*(prices_0.value_num/prices_0
.value_denom) AS 'Solde'

 

FROM gnucash.accounts accounts_0, gnucash.accounts accounts_1,
gnucash.commodities commodities_0, gnucash.prices prices_0, gnucash.splits
splits_0, gnucash.transactions transactions_0

 

WHERE splits_0.account_guid = accounts_0.guid AND splits_0.tx_guid =
transactions_0.guid AND accounts_0.parent_guid = accounts_1.guid AND
prices_0.commodity_guid = commodities_0.guid AND accounts_0.commodity_guid =
commodities_0.guid

 

ORDER BY accounts_0.name, accounts_1.name, prices_0.date

 

**********************

Commodities (Stock values through month and years)

 

SELECT commodities_0.fullname, commodities_0.cusip, month(prices_0.date),
Year(prices_0.date), Value_num/Value_denom AS 'Cours'

 

FROM gnucash.commodities commodities_0, gnucash.prices prices_0

 

WHERE prices_0.commodity_guid = commodities_0.guid

 

ORDER BY commodities_0.cusip, prices_0.date

 

Kind regards

 

Pierre DUVALLET