JOIN Repeating transactions and Split Transactions

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)
Post Reply
Conrado
New MMEX User
Posts: 5
Joined: Thu Jun 26, 2014 4:47 pm
Are you a spam bot?: No

JOIN Repeating transactions and Split Transactions

Post by Conrado »

Hi i'm trying to make a report to show all the repeating transactions, but i have a problem with the split transactions.
When I make a JOIN with the Category and Subcategory tables and the transaction have a split it shows -1

How can i make the JOIN? I couldn't see any links in the database tables.
This is the query that I'm using:

Code: Select all

Select Bd.bdid, bd.TransDate, ac.AccountName, py.PayeeName, bd.TransCode, ca.CategName, sc.SubCategName, bd.NumOccurrences, bd.TransAmount, bd.Notes
from BillsDeposits_v1 AS bd
INNER JOIN Payee_v1 AS py
ON bd.PAYEEID=py.PAYEEID
INNER JOIN AccountList_v1 AS ac
ON bd.AccountId=ac.AccountId
INNER JOIN Category_v1 AS ca
ON bd.CategId=ca.CategId
INNER JOIN SubCategory_v1 AS sc
ON bd.SubCategId=sc.SubCategId
Order By Bd.bdid Asc
query.png
(30.71 KiB) Downloaded 1041 times
table.png
(30.17 KiB) Downloaded 1041 times
Thanks Conrado

Nikolay
MMEX Developer
Posts: 1344
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: JOIN Repeating transactions and Split Transactions

Post by Nikolay »

Here I've merged split categories for checking table

http://forum.moneymanagerex.org/viewtop ... =16&t=5509

left join should help

Conrado
New MMEX User
Posts: 5
Joined: Thu Jun 26, 2014 4:47 pm
Are you a spam bot?: No

Re: JOIN Repeating transactions and Split Transactions

Post by Conrado »

Nikolay wrote:Here I've merged split categories for checking table

http://forum.moneymanagerex.org/viewtop ... =16&t=5509

left join should help
Thanks for the reply. Please tell me is my analisis is right: the colums that JOINS the tables checkingaccount_v1 with the splittransactions_v1 in your query is "left join splittransactions_v1 s on s.transid=c.transid"  (line 28).
The problem is that BillsDeposits_v1 table haven't the column transid or the splittransactions_v1 table haven't the BDID column, so my question is
How the CheckingAccount transactions are generated from the reapeting transactions when they have a split category?

Nikolay
MMEX Developer
Posts: 1344
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: JOIN Repeating transactions and Split Transactions

Post by Nikolay »

Code: Select all

select 
case (case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end) when -1 then ca.categname 
    else ca.categname ||':'||sc.subcategname end category_name
, case ifnull(bd.categid, -1) when -1 then bs.categid else bd.categid end as categ_id
, case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end as subcateg_id
, bd.* 
, ca.*, sc.*
from BillsDeposits_v1 bd
left join budgetsplittransactions_v1 bs on bd.BDID = bs.TRANSID
INNER JOIN Payee_v1 AS py 
    ON bd.PAYEEID=py.PAYEEID
INNER JOIN AccountList_v1 AS ac
    ON bd.AccountId=ac.AccountId
INNER JOIN Category_v1 AS ca
    ON categ_id=ca.CategId
LEFT JOIN SubCategory_v1 AS sc
    ON subcateg_id=sc.SubCategId

Nikolay
MMEX Developer
Posts: 1344
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: JOIN Repeating transactions and Split Transactions

Post by Nikolay »

Nikolay wrote:

Code: Select all

select 
case (case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end) when -1 then ca.categname 
    else ca.categname ||':'||sc.subcategname end category_name
, case ifnull(bd.categid, -1) when -1 then bs.categid else bd.categid end as categ_id
, case ifnull(bd.subcategid, -1) when -1 then ifnull(bs.subcategid,-1) else ifnull(bd.subcategid, -1) end as subcateg_id
, bd.* 
, ca.*, sc.*
from BillsDeposits_v1 bd
left join budgetsplittransactions_v1 bs on bd.BDID = bs.TRANSID
INNER JOIN Payee_v1 AS py 
    ON bd.PAYEEID=py.PAYEEID
INNER JOIN AccountList_v1 AS ac
    ON bd.AccountId=ac.AccountId
INNER JOIN Category_v1 AS ca
    ON categ_id=ca.CategId
LEFT JOIN SubCategory_v1 AS sc
    ON subcateg_id=sc.SubCategId
For more details 
http://forum.moneymanagerex.org/viewtop ... data#p1074

Conrado
New MMEX User
Posts: 5
Joined: Thu Jun 26, 2014 4:47 pm
Are you a spam bot?: No

Re: JOIN Repeating transactions and Split Transactions

Post by Conrado »

Nikolay thanks for the help

Post Reply