Custom SQL

[omalleypat]

I modified Nikolay's "Summary of sub-categories and totals by Categories from date to date " script above so that you can filter by note instead of by date range. I found it helpful to "tag" some of my expenses such as trips. For example when I go on vacation, if I buy a flight months ahead of time and have expenses scattered across dates and categories it is nice to see them grouped. To do this I add a tag to the note field such as #boston to any transactions related to my trip to boston.

Anyway, here is the code, hope it is helpful. I don't really know SQL, but it appears to perform as desired. Like Nikolay's, you need to edit the filter line in two places (I've marked them as #TAG). Also the % is a wildcard so there can be any number of characters before and after the tag.

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 (
      /* Get the non split transactions */
           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
         , ca.notes as notes
           FROM
         checkingaccount_v1 ca
           WHERE
         ca.status not in ('D','V') and
         ca.transcode not in ('Transfer') and
         ca.categid!=-1
           /* Now, union with the split transactions */
      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
         , (select notes from checkingaccount_v1 where transid=st.transid) as notes
           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 tag  in the line below here and in the end of the script:     */
      trx.notes like "%#TAG%" 
        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
         ,ca.notes as notes
                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
         ,(select notes from checkingaccount_v1 where transid=st.transid) as notes
                  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 tag  in the line below:     */
      trx2.notes like "%#TAG%"
   GROUP BY
      trx2.categid
) t
ORDER BY category, t.sequence, Subcategory