Please, change dates in the midle and the bottom of the script under "/* 1 Set your date range in the line below here and in the end of the script: */" line.
Date format is 'YYYY-MM-DD'.
It's script have been checked by me and by bobwill and seem working fine.
- Code: Select all
select (select categname from category_v1 where categid=t.categid) as Category,
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 1 as sequence, categid, subcategid,
sum(trx.transamount*cu.baseconvrate) as Total
from (
select 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 not in ('Transfer') and ca.categid!=-1
union all
select (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
/*a.status='Open' and*/ a.accounttype='Checking' and
/* 1 Set your date range in the line below here and in the end of the script: */
trx.transdate between '2009-01-01' and '2009-12-31'
group by trx.categid, trx.subcategid
union all
select
2 as sequence, trx2.categid, trx2.subcategid,
sum(trx2.transamount*cu.baseconvrate) as Total
from (select 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 (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 /*a.status='Open' and */ a.accounttype='Checking' and
/* 2 Put your date range in the line below: */
trx2.transdate between '2009-01-01' and '2009-12-31'
group by trx2.categid) t
order by category, t.sequence, Subcategory
.