all transaction in one select

Page 1 of 1
4 posts
all transaction in one select

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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;
Re: all transaction in one select

miki
Super MMEX User

Posts: 23
Joined: Wed Jul 11, 2012 9:50 am
Location: IT
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.2.7 x64 [portable mode] / Windows 10 1703 Home x64
Re: all transaction in one select

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
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.
Re: all transaction in one select

miki
Super MMEX User

Posts: 23
Joined: Wed Jul 11, 2012 9:50 am
Location: IT
Ok, thank you :)
MMEX v1.2.7 x64 [portable mode] / Windows 10 1703 Home x64
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest