Custom SQL

[Nikolay]

Some example:

Code: Select all

select currency_symbol currency, baseconvrate rate from currencyformats_v1 where currency_symbol in ('EUR','USD')

This Custom SQL report shows main currencies rates. (Only for version 0.9.4.0 and above)

[madhan]

Another one sent by MiskoB

"How much you spend in category and then detail in subcategory and in end notes for specifying bills to see where you spent lots of money"

Code: Select all

SELECT
  (SELECT c.categname
        FROM category_v1 c
        WHERE c.categid = categ.categid
      ) AS category,
  categ.transamount_sum AS sum_cat,
  (SELECT c.subcategname
        FROM subcategory_v1 c
        WHERE c.subcategid = subcateg.subcategid
      ) AS subcategory,
  subcateg.transamount_sum AS sum_subcat,
  detail.notes AS notes_for_subcategory_description, detail.transamount_sum AS price
FROM
  (select
      COALESCE(sp.categid, ch.categid) AS categid,
      SUM(CASE ch.transcode WHEN 'Deposit' THEN 1
                        WHEN 'Withdrawal' THEN -1
          END * COALESCE(sp.splittransamount, ch.transamount)) as transamount_sum
    from checkingaccount_v1 ch
        LEFT JOIN splittransactions_v1 sp
          ON ch.transid = sp.transid
    where ch.transdate >= date(current_date, 'start of month')
      and ch.transdate < date(current_date, 'start of month', '1 month')
    group by COALESCE(sp.categid, ch.categid)
  ) as categ

JOIN
  (select
      MAX(COALESCE(sp.categid, ch.categid)) AS categid,
      COALESCE(sp.subcategid, ch.subcategid) AS subcategid,
      SUM(CASE ch.transcode WHEN 'Deposit' THEN 1
                        WHEN 'Withdrawal' THEN -1
          END * COALESCE(sp.splittransamount, ch.totransamount)) as transamount_sum
    from checkingaccount_v1 ch
        LEFT JOIN splittransactions_v1 sp
          ON ch.transid = sp.transid
    where ch.transdate >= date(current_date, 'start of month')
      and ch.transdate < date(current_date, 'start of month', '1 month')
    group by COALESCE(sp.subcategid, ch.subcategid)
  ) as subcateg
  ON categ.categid = subcateg.categid

JOIN
  (select
      COALESCE(sp.categid, ch.categid) AS categid,
      COALESCE(sp.subcategid, ch.subcategid) AS subcategid,
      notes,
      CASE ch.transcode WHEN 'Deposit' THEN 1
                        WHEN 'Withdrawal' THEN -1
      END * COALESCE(sp.splittransamount, ch.totransamount) as transamount_sum
    from checkingaccount_v1 ch
        LEFT JOIN splittransactions_v1 sp
          ON ch.transid = sp.transid
    where ch.transdate >= date(current_date, 'start of month')
      and ch.transdate < date(current_date, 'start of month', '1 month')

  ) as detail
  ON categ.categid = detail.categid
    AND subcateg.subcategid = detail.subcategid
WHERE categ.transamount_sum != 0
  AND detail.transamount_sum != 0
ORDER BY sum_cat, category, sum_subcat, subcategory, detail.transamount_sum
 


[Nikolay]

Totals by categories for this month grouped by accounts:

Code: Select all

SELECT
a.accountname account , c.categname category, pfx_symbol||sum(trx.transamount*(case when trx.transcode='Withdrawal' then -1 else 1 end))||cu.sfx_symbol total
FROM (
select ca.status as status,
ca.transcode as transcode, ca.transdate as transdate,
ca.accountid as accountid, ca.categid as categid,
ca.subcategid as subcategid, ca.transamount transamount
FROM checkingaccount_v1 ca where ca.categid!=-1
union all
select (select status from checkingaccount_v1 where transid=st.transid) as status,
(select transcode from checkingaccount_v1 where transid=st.transid) as transcode,
(select transdate from checkingaccount_v1 where transid=st.transid) as transdate,
(select accountid from checkingaccount_v1 where transid=st.transid) as accountid,
st.categid as categid, st.subcategid as subcategid, st.splittransamount transamount  from splittransactions_v1 st) trx , category_v1 c, accountlist_v1 a, currencyformats_v1 cu
WHERE trx.categid=c.categid and trx.status not in ('V','D')
and a.accountid=trx.accountid
and a.currencyid=cu.currencyid
and trx.transdate >= date(current_date, 'start of month')
and trx.transdate < date(current_date, 'start of month', '1 month')
group by trx.categid , trx.accountid
order by 2

Totals by categories

Code: Select all

SELECT
c.categname category, sum(trx.transamount*(cu.baseconvrate)*(case when trx.transcode='Withdrawal' then -1 else 1 end)) as total
FROM (
select ca.status as status,
ca.transcode as transcode, ca.transdate as transdate,
ca.accountid as accountid, ca.categid as categid,
ca.subcategid as subcategid, ca.transamount transamount
FROM checkingaccount_v1 ca
union all
select (select status from checkingaccount_v1 where transid=st.transid) as status,
(select transcode from checkingaccount_v1 where transid=st.transid) as transcode,
(select transdate from checkingaccount_v1 where transid=st.transid) as transdate,
(select accountid from checkingaccount_v1 where transid=st.transid) as accountid,
st.categid as categid, st.subcategid as subcategid, st.splittransamount transamount  from splittransactions_v1 st) trx , category_v1 c, accountlist_v1 a, currencyformats_v1 cu
WHERE trx.categid=c.categid and trx.status not in ('V','D')
and a.accountid=trx.accountid
and a.currencyid=cu.currencyid
and trx.transdate >= date(current_date, 'start of month')
and trx.transdate < date(current_date, 'start of month', '1 month')
group by trx.categid
order by 1

[phildebrand]

I posted this on the google group, but I figured I'd add it here. This will calculate the value of your accounts as of a particular day. You just need to modify the TRANS.DATE query in the where statement.

Code: Select all

SELECT A.ACCOUNTNAME as "Account", ROUND(A.INITIALBAL + SUM(TRANS.AMT),2) as "Balance"

FROM ACCOUNTLIST_V1 A

LEFT JOIN
   (
   SELECT -C.TRANSAMOUNT as AMT, C.ACCOUNTID as ID, C.TRANSDATE as DATE
   FROM CHECKINGACCOUNT_V1 C
   WHERE TRANSCODE='Withdrawal' OR TRANSCODE='Transfer'

   UNION

   SELECT C.TRANSAMOUNT as AMT, C.ACCOUNTID as ID, C.TRANSDATE as DATE
   FROM CHECKINGACCOUNT_V1 C
   WHERE TRANSCODE='Deposit'

   UNION
   
   SELECT C.TRANSAMOUNT as AMT, C.TOACCOUNTID as ID, C.TRANSDATE as DATE
   FROM CHECKINGACCOUNT_V1 C
   WHERE TRANSCODE='Transfer'
   ) TRANS
ON TRANS.ID = A.ACCOUNTID

WHERE A.ACCOUNTTYPE = 'Checking' AND TRANS.DATE<='2008-12-31'

GROUP BY A.ACCOUNTID;


[Nikolay]

Budget Performance for current month
The report takes into account the upcoming and overdue scheduled transactions.

Code: Select all

    select Category,  Subcategory, Estimated, Actual, round(Actual-Estimated,2) as 'Difference', round(Actual/Estimated*100,2)*(abs(Actual-Estimated)/(Actual-Estimated)) as "%"
        from (
        select m.categname as Category, m.subcategname as Subcategory,
        round(ifnull((select  bt.amount * (case when bt.period='Yearly' then 1     when bt.period='Half-Yearly' then 2     when bt.period='Quarterly' then 4     when bt.period='Bi-Monthly' then 6     when bt.period='Bi-Weekly' then 24     when bt.period='Weekly' then 48     when bt.period='None' then 0  else 12 end)/12 from budgettable_v1 bt, budgetyear_v1 b         
        where  bt.categid=m.categid     
        and bt.subcategid=m.subcategid     
        and b.budgetyearname=strftime('%Y','now')     
        and bt.budgetyearid=b.budgetyearid),0),2)
        as Estimated,
        round(ifnull((select sum (transamount)
        from (
        select ca.transdate as transdate,     ca.categid as categid, ca.subcategid as subcategid, ca.transamount*(case when ca.transcode='Withdrawal' then -1 else 1 end)  as transamount   
        FROM checkingaccount_v1 ca   
        where ca.status not in ('D','V')   and ca.categid!=-1 and ca.transcode!='Transfer'
        union all         
        select     (select transdate from checkingaccount_v1 where transid=st.transid) transdate,
        st.categid as categid, st.subcategid as subcategid, st.splittransamount *(case when (select transcode from checkingaccount_v1 where transid=st.transid)='Withdrawal' then -1 else 1 end)  transamount     from splittransactions_v1 st   
        where (select status from checkingaccount_v1 where transid=st.transid) not in ('D','V')
and (select transcode from checkingaccount_v1 where transid=st.transid)!='Transfer'       
        union all         
        select       
        case when bd.nextoccurrencedate<date(current_date, 'start of month') then current_date else bd.nextoccurrencedate end  as transdate,
        bd.categid as categid, bd.subcategid as subcategid, bd.transamount*(case when bd.transcode='Withdrawal' then -1 else 1 end)*(case when bd.repeats='10' then 30 /*'1 days'*/
        when bd.repeats='1' then 4 /*'7 days'*/
        when bd.repeats='2' then 2 /*'14 days'*/
        when bd.repeats='9' then 1/*'28 days'*/ else 1 end) transamount       
        FROM billsdeposits_v1 bd) fintrx           
        where fintrx.transdate>= date(current_date, 'start of month')     
        and fintrx.transdate< date(current_date, 'start of month', '1 month')     and fintrx.categid=m.categid and fintrx.subcategid=m.subcategid           group by fintrx.subcategid ),0),2) as Actual
        from   (select c.categid as categid, c.categname as categname, '-1' as subcategid,'' as subcategname from category_v1 c
    union
    select c.categid as categid,
        c.categname as categname, s.subcategid as subcategid, s.subcategname as subcategname from category_v1 c, subcategory_v1 s
        where c.categid=s.categid) m
        )         
            order by Difference , Category, Subcategory

Have been updated 13 MAR 2009

[Nikolay]

How much I spend each day?

Code: Select all

SELECT p.payeename as Payee,   
case when strftime('%w',trx.transdate)='0' then 'Sunday'  when strftime('%w',trx.transdate)='1' then 'Monday' when strftime('%w',trx.transdate) ='2' then 'Tuesday' when strftime('%w',trx.transdate)='3' then 'Wednesday' when strftime('%w',trx.transdate)='4' then 'Thursday' when strftime('%w',trx.transdate)='5' then 'Friday'   else 'Saturday'  end as day,     trx.transdate as 'date', pfx_symbol||round(sum(trx.transamount*(cu.baseconvrate)*(case when trx.transcode='Withdrawal' then -1 else 1 end)),2)||cu.sfx_symbol total     
FROM (
select ca.payeeid as Payeeid, ca.status as status, ca.transcode as transcode, ca.transdate as transdate, ca.accountid as accountid, ca.categid as categid, ca.subcategid as subcategid, ca.transamount transamount     
FROM checkingaccount_v1 ca    where ca.categid !=-1
union     
select   
(select payeeid from checkingaccount_v1 where transid=st.transid)as Payeeid,   
(select status from checkingaccount_v1 where transid=st.transid) as status,     
(select transcode from checkingaccount_v1 where transid=st.transid) as transcode,     
(select transdate from checkingaccount_v1 where transid=st.transid) as transdate,     (select accountid from checkingaccount_v1 where transid=st.transid) accountid,     st.categid as categid, st.subcategid as subcategid, st.splittransamount transamount  from splittransactions_v1 st) trx , category_v1 c, accountlist_v1 a, currencyformats_v1 cu, payee_v1 p     
WHERE p.payeeid=trx.payeeid and trx.categid=c.categid and trx.status<>'V' and trx.status<>'D'     and trx.transcode='Withdrawal'     and a.accountid=trx.accountid     and a.currencyid=cu.currencyid     and trx.transdate >= date(current_date, 'start of month')     and trx.transdate < date(current_date, 'start of month', '1 month')     group by trx.transdate , trx.payeeid     order by 3,1

[Nikolay]

This report shows the planned transactions for the next 60 days with notes.
It's has some restrictions for daily, weekly and bi-weekly transactions (report will show only 2 planned transactions in this case).

Code: Select all

select a.date, c.categname,
(select s.subcategname from subcategory_v1 s where s.subcategid=a.subcategid) as Subcategory,
a.amount, a.notes from(
select
bd.categid as categid,
bd.subcategid as subcategid,
bd.nextoccurrencedate as Date,
bd.transamount*(case when bd.transcode='Withdrawal' then -1 else 1 end)as Amount,
bd.notes as Notes
from billsdeposits_v1 bd
union
select
bd.categid as categid,
bd.subcategid as subcategid,
date(bd.nextoccurrencedate, case when bd.repeats='3' then '1 month'
when bd.repeats='5' then '3 month'
when bd.repeats='4' then '2 month'
when bd.repeats='6' then '6 month'
when bd.repeats='8' then '4 month'
when bd.repeats='10' then '1 days'
when bd.repeats='1' then '7 days' 
when bd.repeats='2' then '14 days'
when bd.repeats='9' then '28 days'
end) as Date,
bd.transamount*(case when bd.transcode='Withdrawal' then -1 else 1 end)as Amount,
(bd.notes||case
when bd.repeats='10' then ' *** and every day '
when bd.repeats='1' then ' *** and every week ' 
when bd.repeats='2' then ' *** and every 2 weeks '  else ' ' end) as Notes
from billsdeposits_v1 bd
where bd.numoccurrences>1 or bd.numoccurrences =-1
) a , category_v1 c where
c.categid=a.categid and
date>=date(current_date, 'start of month') 
and a.date < date(current_date, '60 days')
order by 1,2


[Nikolay]

This report will shows how many Payee spent this month. Results grouped weekly.

Code: Select all

 SELECT p.payeename as Payee, strftime('%W',trx.transdate) as 'week_no',
strftime("%d/%m", min(trx.transdate))||'--'|| strftime("%d/%m", max(trx.transdate)) as date_range,
pfx_symbol||round(sum(trx.transamount*(cu.baseconvrate)*
(case when trx.transcode='Withdrawal' then -1 else 1 end)),2)||cu.sfx_symbol as total     
FROM (
select ca.payeeid as Payeeid, ca.status as status, ca.transcode as transcode,
ca.transdate as transdate, ca.accountid as accountid, ca.categid as categid,
ca.subcategid as subcategid, ca.transamount transamount     
FROM checkingaccount_v1 ca    where ca.categid<>-1
union all   
select   
(select payeeid from checkingaccount_v1 where transid=st.transid)as Payeeid,   
(select status from checkingaccount_v1 where transid=st.transid) as status,     
(select transcode from checkingaccount_v1 where transid=st.transid) as transcode,     
(select transdate from checkingaccount_v1 where transid=st.transid) as transdate,     
(select accountid from checkingaccount_v1 where transid=st.transid) accountid,     
st.categid as categid, st.subcategid as subcategid, st.splittransamount transamount 
from splittransactions_v1 st) trx , category_v1 c, accountlist_v1 a,
currencyformats_v1 cu, payee_v1 p     
WHERE p.payeeid=trx.payeeid and trx.categid=c.categid and trx.status not in ('V' ,'D')     
and trx.transcode='Withdrawal'     
and a.accountid=trx.accountid     
and a.currencyid=cu.currencyid     
--Put your date range here for example
--and trx.transdate between '2009-01-01' and '2009-02-01'
and trx.transdate between  date(current_date, 'start of month','-1 month')   and date(current_date, 'start of month', '2 month')     
group by week_no , trx.payeeid     
order by 2,1

[Nikolay]

Summary of sub-categories and totals by Categories from date to date:

Code: Select all

            select (select categname from category_v1 where categid=t.categid) as Categname,
            case when t.subcategid=-1 then ' ' else ifnull((select s.subcategname from subcategory_v1 s where s.subcategid=t.subcategid),'Category Total:' ) end as Subcategory,
            round(total,2) as Total
            from (
            select
            sequence, categid, subcategid,
            sum(trx.transamount*cu.baseconvrate) as Total
            from (
            select 1 as sequence,
            ca.transdate as transdate,
            ca.categid as categid,
            ca.subcategid as subcategid,
            ca.accountid as accountid,
            ca.transamount*(case when ca.transcode='Withdrawal' then -1.00 else 1.00 end) as transamount
            FROM checkingaccount_v1 ca
            where ca.status not in ('D','V') and ca.transcode!='Transfer' and ca.categid!=-1
            union all
            select 1 as sequence,
            (select transdate from checkingaccount_v1 where transid=st.transid) as transdate,
            st.categid as categid,
            st.subcategid as subcategid,
            (select accountid from checkingaccount_v1 where transid=st.transid) as accountid,
            st.splittransamount *(case when (select transcode from checkingaccount_v1 where transid=st.transid)='Withdrawal' then -1.00 else 1.00 end) as transamount
            from splittransactions_v1 st
            where (select status from checkingaccount_v1 where transid=st.transid) not in ('D','V')
            and (select transcode from checkingaccount_v1 where transid=st.transid) != ('Transfer')) trx, accountlist_v1 a, currencyformats_v1 cu
            where a.accountid=trx.accountid and a.currencyid=cu.currencyid and
            --Put your date range here:
            trx.transdate between '2009-01-01' and '2009-12-31'
            group by trx.categid, trx.subcategid
            union all
            select
            trx2.sequence, trx2.categid,
            trx2.subcategid,
            sum(trx2.transamount*cu.baseconvrate) as Total
            from (
            select 2 as sequence,
            ca.transdate as transdate,
            ca.categid as categid,
            1111 as subcategid,
            ca.accountid as accountid,
            ca.transamount*(case when ca.transcode='Withdrawal' then -1.00 else 1.00 end) as transamount
            FROM checkingaccount_v1 ca
            where ca.status not in ('D','V')
        and ca.transcode!='Transfer' and ca.categid!=-1
            union all
            select 2 as sequence,
            (select transdate from checkingaccount_v1 where transid=st.transid) as transdate,
            st.categid as categid,
            1111 as subcategid,
            (select accountid from checkingaccount_v1 where transid=st.transid) as accountid,
            st.splittransamount*(case when (select transcode from checkingaccount_v1 where transid=st.transid)='Withdrawal' then -1.00 else 1.00 end) as transamount
            from splittransactions_v1 st
            where (select status from checkingaccount_v1 where transid=st.transid) not in ('D','V') and (select transcode from checkingaccount_v1 where transid=st.transid) not in ('Transfer')) trx2, accountlist_v1 a, currencyformats_v1 cu
            where a.accountid=trx2.accountid and a.currencyid=cu.currencyid and
            --Put your date range here:
            trx2.transdate between '2009-01-01' and '2009-12-31'
            group by trx2.categid) t
            order by categname, t.sequence, Subcategory

[madhan]

Nikolay, That is a pretty good one!