Difference between revisions of "GnuCash SQL Examples"

sudo su postgres # switch to the postgres account to have admin rights in the database
psql             # open the sql shell, you can also call psql gnucash, to directly connect to the gnucash database right away

-- now we are in the psql shell. So comments start with double dash -- instead of pound #

postgres=# \c gnucash -- connect to the gnucash database
gnucash=# CREATE ROLE gnucashdbreader WITH ENCRYPTED PASSWORD 'randomUserPassword' LOGIN; -- new user account, which may login
CREATE ROLE -- response from the system
gnucash=# GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO gnucashdbreader; -- You must be connected to the database gnucash, while doing this. PUBLIC is context specific and refers to the current database

select distinct s1.value_num, s1.value_denom, t1.post_date, t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name;

select distinct CAST((CAST(-s2.value_num as DECIMAL)/s2.value_denom) as DECIMAL(10,2)) as amount, t1.post_date as date , t1.description,
a1.description, a1.name, a1.account_type,
a2.description, a2.name, a2.account_type
from splits s1
inner join splits s2 on s1.tx_guid=s2.tx_guid
inner join transactions t1 on t1.guid=s2.tx_guid
inner join accounts a1 on s1.account_guid=a1.guid
inner join accounts a2 on s2.account_guid=a2.guid
where a1.name!=a2.name

SELECT SUM(CAST(-CAST(s2.value_num AS DECIMAL(10,2))/s1.value_denom AS DECIMAL(10,2))) AS amount
FROM splits s1
INNER JOIN splits s2 ON s1.tx_guid=s2.tx_guid
INNER JOIN transactions t1 ON t1.guid=s2.tx_guid
INNER JOIN accounts a1 ON s1.account_guid=a1.guid
INNER JOIN accounts a2 ON s2.account_guid=a2.guid
WHERE a1.name!=a2.name AND t1.post_date >= '<some start date>' AND t1.post_date <= '<some end date>' AND a1.name = '<category>';

with recursive path_list as (
   select guid, parent_guid,name,concat(name) as path
   from accounts
   where parent_guid is null  -- this defines the start of the recursion
   union all
   select child.guid,child.parent_guid,child.name,concat(path,':',child.name)
   from accounts as child
     join path_list as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select guid from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';

select path from path_list where guid='9bea76f63e5f1caa9b36c4d5e9d0a721';

select * from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';

with recursive all_nested_accounts as (
   select guid as start_guid,guid, parent_guid
   from accounts
   union all
   select start_guid,child.guid,child.parent_guid
   from accounts as child
     join all_nested_accounts as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select * from all_nested_accounts where start_guid = '31e4ca3657b94feabe31054757aa7a5a';

select a.name,b.name,c.name from all_nested_accounts as ana join accounts as a on ana.start_guid=a.guid join accounts as b on ana.guid=b.guid join accounts as c on ana.parent_guid=c.guid where start_guid = '31e4ca3657b94feabe31054757aa7a5a';

select * from (select tx_guid, count(tx_guid) as tx_count from splits group by tx_guid) as split_lookup_preselect where tx_count>2;

SELECT c1.namespace AS 'Namespace',
  c1.mnemonic || ' (' || c1.fullname || ')' AS 'Security',
  c2.mnemonic || ' (' ||  c2.fullname || ')' AS 'Currency',
  p.date AS 'Date', p.source AS 'Source', p.type AS 'Type',
  p.value_num * 1.0 / p.value_denom  AS 'Price Decimal',
  p.value_num AS 'Price Numerator', p.value_denom AS 'Price Denominator'
FROM prices AS p
JOIN commodities AS c1 ON p.commodity_guid = c1.guid 
JOIN commodities AS c2 ON p.currency_guid = c2.guid
ORDER BY c1.namespace, c1.mnemonic, p.date