all transaction in one select

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

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

all transaction in one select

Post by Nikolay »

alldata from old topic

Code: Select all

    select CANS.TransID as ID, CANS.TransCode as TransactionType, CANS.TransDate as Date, round(strftime('%Y', CANS.TransDate)) as Year
       , round(strftime('%m', CANS.TransDate)) as Month, round(strftime('%d', CANS.TransDate)) as Day, CAT.CategName as Category, SUBCAT.SubCategName as Subcategory
       , ROUND(case CANS.TRANSCODE when 'Withdrawal' then -1*CANS.TRANSAMOUNT  else CANS.TRANSAMOUNT end,2) as Amount 
       , (select cf.BaseConvRate from currencyformats_v1 cf where cf.currencyid=FROMACC.CURRENCYID) as BaseConvRate
       , FROMACC.CurrencyID, FROMACC.AccountName,  FROMACC.AccountID, TOACC.AccountName as ToAccountName, TOACC.ACCOUNTId as ToAccountID, CANS.ToTransAmount, TOACC.CURRENCYID as ToCurrencyID
       , 0 as Splitted , CAT.CategID as CategID, SUBCAT.SubCategID as SubcategID, PAYEE.PayeeName as Payee, PAYEE.PayeeID
       , CANS.TRANSACTIONNUMBER as TransactionNumber, CANS.Status as Status, CANS.NOTES as Notes
        from  CHECKINGACCOUNT_V1 CANS
        inner join CATEGORY_V1    CAT     on CAT.CATEGID      = CANS.CATEGID
        left  join SUBCATEGORY_V1 SUBCAT  on SUBCAT.SUBCATEGID= CANS.SUBCATEGID
        left  join PAYEE_V1       PAYEE   on PAYEE.PAYEEID    = CANS.PAYEEID
        left  join ACCOUNTLIST_V1 FROMACC on FROMACC.ACCOUNTID= CANS.ACCOUNTID
        left  join ACCOUNTLIST_V1 TOACC   on TOACC.ACCOUNTID  = CANS.TOACCOUNTID
       UNION ALL
        SELECT CASS.TRANSID, CASS.TRANSCODE, CASS.TransDate, round(strftime('%Y', CASS.TransDate)) as Year
       , round(strftime('%m', CASS.TransDate)) as Month, round(strftime('%d', CASS.TransDate)) as Day,CAT.CATEGNAME, SUBCAT.SUBCATEGNAME
       ,ROUND(SPLIT.SPLITTRANSAMOUNT, 2)*(case when CASS.TRANSCODE='Withdrawal' then -1 else 1 end) as Amount 
       , (select cf.BaseConvRate from currencyformats_v1 cf where cf.currencyid=FROMACC.CURRENCYID) as BaseConvRate
       , FROMACC.CURRENCYID, FROMACC.ACCOUNTNAME, FROMACC.ACCOUNTId ,TOACC.ACCOUNTNAME, TOACC.ACCOUNTId, CASS.totransamount, TOACC.CURRENCYID 
       , 1 as Splitted , CAT.CATEGId, SUBCAT.SUBCATEGId, PAYEE.PAYEENAME, PAYEE.PAYEEID
       , CASS.TRANSACTIONNUMBER, CASS.Status, CASS.NOTES 
       from  CHECKINGACCOUNT_V1 CASS
        inner join SPLITTRANSACTIONS_V1 SPLIT   on SPLIT.TRANSID    = CASS.TRANSID
        inner join CATEGORY_V1          CAT     on CAT.CATEGID      = SPLIT.CATEGID
        left  join SUBCATEGORY_V1       SUBCAT  on SUBCAT.SUBCATEGID= SPLIT.SUBCATEGID
        left  join PAYEE_V1             PAYEE   on PAYEE.PAYEEID    = CASS.PAYEEID
        left  join ACCOUNTLIST_V1       FROMACC on FROMACC.ACCOUNTID= CASS.ACCOUNTID
        left  join ACCOUNTLIST_V1       TOACC   on TOACC.ACCOUNTID  = CASS.TOACCOUNTID
       order by CANS.transid;
miki
New MMEX User
Posts: 14
Joined: Wed Jul 11, 2012 3:50 pm
Are you a spam bot?: No
Location: IT
Contact:

Re: all transaction in one select

Post by miki »

mmm ... I have 3687 operation and the test button works fine,
but when I open it I have mmex freezed and keep 50% CPU (version 1.1.0 RC3)

Regards

Miki
MMEX v1.7.0 x64 [portable mode] / Windows 10 22H2 Home x64
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: all transaction in one select

Post by Nikolay »

Becouse there is no linits for line numbers of sql query.
As result mmex attempt to crease html report with very big size.
U should use where bla bla bla to get anly needed data.
miki
New MMEX User
Posts: 14
Joined: Wed Jul 11, 2012 3:50 pm
Are you a spam bot?: No
Location: IT
Contact:

Re: all transaction in one select

Post by miki »

Ok, thank you :)
MMEX v1.7.0 x64 [portable mode] / Windows 10 22H2 Home x64
Post Reply