Difference between revisions of "GnuCash SQL Examples"

From GnuCash
Jump to: navigation, search
(added some content.)
Line 62: Line 62:
 
== Return a account guid by path ==
 
== Return a account guid by path ==
  
−
This returns a guid of an account by its path. It doesn't work perfectly as there is no real order of tuples in SQL. Therefore, if a path like Aktiva>Superbank>Supercar exists and a path like Aktiva>Supercar>Superbank as well, it won't work, as it does not return only one value. (a, e, j) is the same as (e, j, a), (j, e, a) or other orders. If a name for a bank account appears more than once in the account structure, it won't have any negative effect, as only connected chains are regarded.
+
This returns a guid of an account by its path. The path must unlike in the GnuCash frontend start with 'Root Account'.  
  
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
−
with path as (values('a'), ('e'), ('j')),
+
with recursive path_list as (
−
path_ancestors as (select guid, parent_guid from accounts where name in (select * from path)),
+
  select guid, parent_guid,name,concat(name) as path
−
endnodes as (
+
  from accounts
−
  select * from path_ancestors
+
  where parent_guid is null  -- this defines the start of the recursion
−
  where guid not in (
+
  union all
−
    select s.guid from path_ancestors as s where s.guid in (
+
  select child.guid,child.parent_guid,child.name,concat(path,':',child.name)
−
      select p.parent_guid from path_ancestors as p)
+
  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
−
  and (select count(*) from path) in (select count (*) from path_ancestors))
+
)
−
select * from endnodes where (select count(*) from endnodes) in ((1));
+
select guid from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
You can check the output and have a bit more insight by replacing the last line with
 +
<syntaxhighlight lang="sql">
 +
select * from path_list where path='Root Account:Aktiva:Barvermögen:Mein Hauptkonto';
 +
</syntaxhighlight>
 +
  
  

Revision as of 20:14, 2 February 2022

Introduction

This page is under construction.

Template


Request all transactions like shown in the account list

This request gives you all transactions, but transactions, which involve more than two accounts, will show up in the wrong way. If s1.value_num is replaced by s2.value_num, you will have the right amounts, but wrong signed. 

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;

To get the right amounts correctly signed adjusted by the factor for the currency, we have to use this sql-querry: 

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

Request expenses for a certain period and category

If you just want to know, how much you spend on certain things during some period, you can use this querry: 

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>';



Return a account guid by path

This returns a guid of an account by its path. The path must unlike in the GnuCash frontend start with 'Root Account'. 

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';

You can check the output and have a bit more insight by replacing the last line with 

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



Request all child accounts of a given account

This request will result in a list of the start account and all children and grand*x*children. The snippet is based on a snippet from sql-workbench. 


with recursive account_tree as (
   select guid, parent_guid
   from accounts
   where guid = 'Start account'  -- this defines the start of the recursion
   union all
   select child.guid,
          child.parent_guid
   from accounts as child
     join account_tree as parent on parent.guid = child.parent_guid -- the self join to the CTE builds up the recursion
)
select * from account_tree;


Request the guid of all multi-transactions

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;
Retrieved from "https://wiki.gnucash.org/wiki/index.php?title=GnuCash_SQL_Examples&oldid=20930"