Reports to compare analogous period from current month with X previous months

[vomikan]

SQL stage 1

with tr as (
    select 
         strftime('%m', TRANSDATE) as month
         , strftime('%Y', TRANSDATE) as Year
     , sum((case c.categid when -1 then  splittransamount else  transamount  end) 
            * cf.BaseConvRate
         ) amount
    from checkingaccount_v1 c
    left join splittransactions_v1 s on s.transid=c.transid
    left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID=c.ACCOUNTID
    left join currencyformats_v1 cf on cf.currencyid=AC.currencyid
    where transcode = 'Withdrawal'
    and c.status !='V'
    and ac.status !='Closed'
    and (date('now', 'start of month','-35 month','localtime') <= transdate
        and transdate < date('now', 'start of month','+1 month','localtime'))
    and strftime('%d', (date('now', 'localtime'))<=  strftime('%d', TRANSDATE)
        and transdate < date('now', 'start of month','+1 month','localtime'))
    group by month, year)
, d as (select '01' as month, 1 as ID
union select '02', 2
union select '03', 3
union select '04', 4
union select '05', 5
union select '06', 6
union select '07', 7
union select '08', 8
union select '09', 9
union select '10', 10
union select '11', 11
union select '12', 12
order by ID
)
select d.ID
,ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','localtime'))), 0) as "year"
, ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','-12 month','localtime'))), 0) as "year-1"
, ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','-24 month','localtime'))),0) as "year-2"
from d

[vomikan]

@ggraziotti, Could you test this SQL in GRM?

[ggraziotti]

I will test it next Saturday, tks

[ggraziotti]

On the DB, desktop software version 1.4.0, I do not have this table, but transaction are in "ALL DATA"or "CHECKINGACCOUNT_V1". I do not have "transactions" table. With table I have to export and import ?

[ggraziotti]

Sorry post error

[ggraziotti]

I tested the code today 5 febbrary 2018. So I expected a report for income/expenses from 1th to 5th days of each months. The report would be better detailed for income and expenses and on each category.

There are 2 main problem:

1. the report for previous months report the data for all the month, not only the part for period from1 to 5 days, of each the previous months.
2. The report qork only on expenses and not on income.

tks

giorgio

[vomikan]

@ggraziotti I've made correction

with tr as (
    select 
         strftime('%m', TRANSDATE) as month
         , strftime('%Y', TRANSDATE) as Year
    , sum((case c.categid when -1 then  splittransamount else  transamount  end) 
          * cf.BaseConvRate
         ) amount
    from checkingaccount_v1 c
    left join splittransactions_v1 s on s.transid=c.transid
    left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID=c.ACCOUNTID
    left join currencyformats_v1 cf on cf.currencyid=AC.currencyid
    where transcode = 'Withdrawal'
    and c.status !='V'
    and ac.status !='Closed'
    and (date('now', 'start of month','-35 month','localtime') <= transdate
        and transdate < date('now', 'start of month','+1 month','localtime'))
    and strftime('%d', date('now', 'localtime'))>=  strftime('%d', TRANSDATE)
    group by month, year)
, d as (select '01' as month, 1 as ID
union select '02', 2
union select '03', 3
union select '04', 4
union select '05', 5
union select '06', 6
union select '07', 7
union select '08', 8
union select '09', 9
union select '10', 10
union select '11', 11
union select '12', 12
order by ID
)
select d.ID
,ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','localtime'))), 0) as "year"
, ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','-12 month','localtime'))), 0) as "year-1"
, ifnull((select tr.amount from tr where tr.month=d.month and tr.year
    =strftime('%Y', date('now', 'start of month','-24 month','localtime'))),0) as "year-2"
from d

[ggraziotti]

I Will try today,

Tks

Giorgio

Il 07 feb 2018 13:56, "Nikolay" notifications@github.com ha scritto:

@ggraziotti https://github.com/ggraziotti I've made correction

with tr as ( select strftime('%m', TRANSDATE) as month , strftime('%Y', TRANSDATE) as Year , sum((case c.categid when -1 then splittransamount else transamount end)

• cf.BaseConvRate ) amount from checkingaccount_v1 c left join splittransactions_v1 s on s.transid=c.transid left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID=c.ACCOUNTID left join currencyformats_v1 cf on cf.currencyid=AC.currencyid where transcode = 'Withdrawal' and c.status !='V' and ac.status !='Closed' and (date('now', 'start of month','-35 month','localtime') <= transdate and transdate < date('now', 'start of month','+1 month','localtime')) and strftime('%d', date('now', 'localtime'))>= strftime('%d', TRANSDATE) group by month, year) , d as (select '01' as month, 1 as ID union select '02', 2 union select '03', 3 union select '04', 4 union select '05', 5 union select '06', 6 union select '07', 7 union select '08', 8 union select '09', 9 union select '10', 10 union select '11', 11 union select '12', 12 order by ID ) select d.ID ,ifnull((select tr.amount from tr where tr.month=d.month and tr.year =strftime('%Y', date('now', 'start of month','localtime'))), 0) as "year" , ifnull((select tr.amount from tr where tr.month=d.month and tr.year =strftime('%Y', date('now', 'start of month','-12 month','localtime'))), 0) as "year-1" , ifnull((select tr.amount from tr where tr.month=d.month and tr.year =strftime('%Y', date('now', 'start of month','-24 month','localtime'))),0) as "year-2" from d

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/moneymanagerex/general-reports/issues/44#issuecomment-363760590, or mute the thread https://github.com/notifications/unsubscribe-auth/ARWwmSTtTN7_IxQd0tdPllYr674o3o6mks5tSZ11gaJpZM4PtPMx .

[ggraziotti]

On year -2 the data output are wong. Can u insert also report data detailed by category for every month ?

Should be better to insert this report on "Upper report bar"...

tks giorgio

2018-02-07 13:56 GMT+01:00 Nikolay notifications@github.com:

@ggraziotti https://github.com/ggraziotti I've made correction

with tr as ( select strftime('%m', TRANSDATE) as month , strftime('%Y', TRANSDATE) as Year , sum((case c.categid when -1 then splittransamount else transamount end)

• cf.BaseConvRate ) amount from checkingaccount_v1 c left join splittransactions_v1 s on s.transid=c.transid left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID=c.ACCOUNTID left join currencyformats_v1 cf on cf.currencyid=AC.currencyid where transcode = 'Withdrawal' and c.status !='V' and ac.status !='Closed' and (date('now', 'start of month','-35 month','localtime') <= transdate and transdate < date('now', 'start of month','+1 month','localtime')) and strftime('%d', date('now', 'localtime'))>= strftime('%d', TRANSDATE) group by month, year) , d as (select '01' as month, 1 as ID union select '02', 2 union select '03', 3 union select '04', 4 union select '05', 5 union select '06', 6 union select '07', 7 union select '08', 8 union select '09', 9 union select '10', 10 union select '11', 11 union select '12', 12 order by ID ) select d.ID ,ifnull((select tr.amount from tr where tr.month=d.month and tr.year =strftime('%Y', date('now', 'start of month','localtime'))), 0) as "year" , ifnull((select tr.amount from tr where tr.month=d.month and tr.year =strftime('%Y', date('now', 'start of month','-12 month','localtime'))), 0) as "year-1" , ifnull((select tr.amount from tr where tr.month=d.month and tr.year =strftime('%Y', date('now', 'start of month','-24 month','localtime'))),0) as "year-2" from d

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/moneymanagerex/general-reports/issues/44#issuecomment-363760590, or mute the thread https://github.com/notifications/unsubscribe-auth/ARWwmSTtTN7_IxQd0tdPllYr674o3o6mks5tSZ11gaJpZM4PtPMx .