Income vs Expenses last 12 month

Page 1 of 1
10 posts
Income vs Expenses last 12 month

Nikolay
MMEX Developer

Posts: 2296
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Updated 4-feb-2011
Code: Select all
    select
    date,
    total(case when amount>=0 then amount else 0 end) as  Income,
    total(case when amount<0 then amount else 0 end) as  Expence,
    total(amount) as Difference
    ,''
    from
            (select  strftime('%Y-%m', CANS.TransDate,'localtime') as Date,
            ROUND(total(
       (case CANS.TRANSCODE when 'Withdrawal' then -1 else 1 end)
       *(case CANS.CATEGID when -1 then st.splittransamount else CANS.TRANSAMOUNT end)
       *cf.BaseConvRate),2) as Amount,
       (case CANS.CategId when -1 then st.CategId else CANS.CategId end) as CategID,
            (case CANS.SubCategID when -1 then st.subCategId else CANS.SubCategID end) as SubCategID
            from  CHECKINGACCOUNT_V1 CANS
            left join ACCOUNTLIST_V1 ACC on ACC.ACCOUNTID = CANS.ACCOUNTID
            left join currencyformats_v1 cf on cf.currencyid=ACC .currencyid
       left join splittransactions_v1 st on CANS.transid=st.transid
       where  CANS.TRANSCODE <>'Transfer'
       and  ACC.ACCOUNTTYPE in ('Checking','Term')
       and  CANS.STATUS<>'V'
       and  CANS.TRANSDATE>= date('now', 'start of month','-11 month','localtime')
       and  CANS.TRANSDATE< date('now','start of month', '+1 month', 'localtime')
       group by CATEGID, SUBCATEGID, Date
       )
       group by Date
       order by date
 
Re: Income vs Expenses last 12 month

omalleypat
MMEX Developer

Posts: 164
Joined: Tue Jul 28, 2009 4:34 pm
Location: Atchison, KS
Nikolay,

I like this query, I modified it by removing the two lines that restrict it to last 12 months so I get a view of all time. I wanted to make a graph in OpenOffice, but when I run the query there, it gives syntax errors. You mentioned in one other post that there was special syntax for ooo, is that the problem? I don't know what you meant by that. Works fine in mmex, but when pasted into ooo, it doesn't.

Pat
Re: Income vs Expenses last 12 month

Nikolay
MMEX Developer

Posts: 2296
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
I don't remember.. but double quotes should be used.
Look at my posting viewtopic.php?f=12&t=610
Code: Select all
select
    "date",
    total(case when "amount">=0 then "amount" else 0 end) as  "Income",
.....
Re: Income vs Expenses last 12 month

omalleypat
MMEX Developer

Posts: 164
Joined: Tue Jul 28, 2009 4:34 pm
Location: Atchison, KS
I just clicked the button for "Run SQL Command Directly" and it worked with no modifications.
Re: Income vs Expenses last 12 month

Nikolay
MMEX Developer

Posts: 2296
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Code: Select all
case CANS.TRANSCODE when 'Withdrawal' then -1 else 1 end

should be
Code: Select all
case when CANS.TRANSCODE='Withdrawal' then -1 else 1 end
Re: Income vs Expenses last 12 month

Nikolay
MMEX Developer

Posts: 2296
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Stupid ooo/libre office restrictions!!! Sql editor can't say what is wrong in syntax.
We can't use without doudle qoutes:
t as alias (h, for example, we can)
date, time - strftime(('%Y-%m-%d should be used instead date function
case should be used as described above.

Code: Select all
select "t"."date" as "Date",
total(case when "t".Amount>=0 then "t".Amount else 0 end) as  "Income",
total(case when "t".Amount<0 then "t".Amount else 0 end) as  "Expence",
total("t"."amount") as "Difference" 
from (select   
strftime('%Y-%m', CANS.TransDate,'localtime') as "Date"
,ROUND(total((case when CANS.TRANSCODE='Withdrawal' then -1 else 1 end)
           * (case when CANS.CATEGID=-1 then st.splittransamount else CANS.TRANSAMOUNT end)
           * cf.BaseConvRate),2) as "Amount"
,(case when CANS.CategId=-1 then st.CategId else CANS.CategId end) as "CategID"
,(case when CANS.SubCategID=-1 then st.subCategId else CANS.SubCategID end) as SubCategID

                from  CHECKINGACCOUNT_V1 CANS
                left join ACCOUNTLIST_V1 ACC on ACC.ACCOUNTID = CANS.ACCOUNTID
                left join currencyformats_v1 cf on cf.currencyid=ACC .currencyid
           left join splittransactions_v1 st on CANS.transid=st.transid
where  CANS.TRANSCODE <>'Transfer'
           and  ACC.ACCOUNTTYPE in ('Checking','Term')
           and  CANS.STATUS<>'V'
           and  CANS.TRANSDATE >= strftime('%Y-%m-%d','now', 'start of month','-11 month','localtime')
           and  CANS.TRANSDATE< strftime('%Y-%m-%d','now','start of month', '+1 month', 'localtime')
           group by "CATEGID", "SUBCATEGID", "Date"
           ) as "t"
group by "Date"
order by "Date"
Re: Income vs Expenses last 12 month

omalleypat
MMEX Developer

Posts: 164
Joined: Tue Jul 28, 2009 4:34 pm
Location: Atchison, KS
You're right, it is very frustrating that OOO doesn't even tell you what line the syntax error is on! Makes debugging almost impossible...
Re: Income vs Expenses last 12 month

n7wwk1
New MMEX User

Posts: 2
Joined: Sat Feb 11, 2012 9:07 am
I would like to add the Account name to the script to. Please Help if possible

Thanks
Re: Income vs Expenses last 12 month

n7wwk1
New MMEX User

Posts: 2
Joined: Sat Feb 11, 2012 9:07 am
I would like to add the accountname to this report but am having trouble. Any help would be appreciated. Thanks in advance Bob

Code: Select all
select
    total(case when amount>=0 then amount else 0 end) as  Income,
    total(case when amount<0 then amount else 0 end) as  Expence,
    total(amount) as Net
    ,''
    from
            (select  strftime('%Y-%m', CANS.TransDate,'localtime') as Date,
            ROUND(total(
       (case CANS.TRANSCODE when 'Withdrawal' then -1 else 1 end)
       *(case CANS.CATEGID when -1 then st.splittransamount else CANS.TRANSAMOUNT end)
       *cf.BaseConvRate),2) as Amount,
       (case CANS.CategId when -1 then st.CategId else CANS.CategId end) as CategID,
            (case CANS.SubCategID when -1 then st.subCategId else CANS.SubCategID end) as SubCategID
            from  CHECKINGACCOUNT_V1 CANS
            left join ACCOUNTLIST_V1 ACC on ACC.ACCOUNTID = CANS.ACCOUNTID
            left join currencyformats_v1 cf on cf.currencyid=ACC .currencyid
       left join splittransactions_v1 st on CANS.transid=st.transid
       where  CANS.TRANSCODE <>'Transfer'
       and  ACC.ACCOUNTTYPE in ('Checking','Term')
       and  CANS.STATUS<>'V'
       and  CANS.TRANSDATE>= date('now', 'start of month','-11 month','localtime')
       and  CANS.TRANSDATE< date('now','start of month', '+1 month', 'localtime')
       group by CATEGID, SUBCATEGID, Date
       )
       group by accountname
       order by date
Re: Income vs Expenses last 12 month

Vidura
New MMEX User

Posts: 1
Joined: Wed Mar 27, 2013 3:47 pm
Please tell me how you could edit this so I can fix it between 2 dates that i need for the month.
I want it to be able to display income vs expenses say from 28th to 28th rather than 1st to 1st.

Money manager doesnt have an option to set the start dates of the month to a date I prefer so this would be awesome. PLEASE HELP ME! thanks

.................................................................................................................................
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest