Dan Connolly  committed 1e8b958

- factor out tx_split_detail join between splits, accounts, transactions
- found hundreds/thousands more matches after importing other 2 main accounts

  • Participants
  • Parent commits d96faeb
  • Branches default

Comments (0)

Files changed (1)

File mint_match.sql

  * Transactions
+create or replace view tx_split_detail as
+select tx.post_date, account_name, tx.description, s.memo, s.value_num / s.value_denom amount
+     , tx.guid tx_guid, s.guid split_guid, a.guid account_guid, value_num
+from transactions tx
+join splits s on s.tx_guid = tx.guid
+join accounts a on s.account_guid = a.guid;
+/* Check balances */
+select sum(amount), account_name
+from tx_split_detail
+group by name
+order by name;
 /* Aside: Which transactions from mint accounts don't have OFX online_id's?
  * They seem to all be transfers, which I think I messed around with manually. */
-select tx.post_date,, s.memo, s.value_num / s.value_denom amount
-from transactions tx
-join splits s on s.tx_guid = tx.guid
-join accounts a on s.account_guid = a.guid
- and in (select distinct account from minttrx)
-left join slots on slots.obj_guid = s.guid and = 'online_id'
-where slots.obj_guid is null;
+select td.*
+from tx_split_detail td
+left join slots on slots.obj_guid = td.split_guid and = 'online_id'
+where td.account_name in (select distinct account from minttrx)
+and slots.obj_guid is null;
 -- alter table minttrx drop index minttrx_sig;
 /* Find various descriptions of each transaction split.
  * Denormalize a bit, while we're at it. */
 create or replace view tx_desc as
-select spa.guid, tx.guid tx_guid, tx.post_date, a.guid account_guid, account, value_num
+select td.split_guid guid, td.tx_guid, td.post_date, td.account_guid, td.account_name account, td.value_num
      /* My bank repeats the OFX NAME in the MEMO, but Discover doesn't Ugh. */
-     , tx.description
-     , case when spa.memo > '' then spa.memo else null end memo
+     , td.description
+     , case when td.memo > '' then td.memo else null end memo
      , case when ofxmemo.obj_guid is not null and ofxmemo.string_val like '%Memo:%'
        then substring_index(ofxmemo.string_val, 'Memo:', -1)
        else null
        end ofx_memo
-from transactions tx
-join splits spa on spa.tx_guid = tx.guid
-left join slots ofxmemo on ofxmemo.obj_guid = tx.guid
-     and = 'notes'
-join accounts a on spa.account_guid = a.guid;
+from tx_split_detail td
+left join slots ofxmemo on ofxmemo.obj_guid = td.tx_guid
+     and = 'notes';
 drop table if exists mint_gc_matches;
 create table mint_gc_matches (
 left join mint_gc_matches mm
   on =
 where is null
-  and mx.isChild = 0
+  and mx.isChild = 0 and mx.children is null
   -- spaces, &
   and not in ('296017984', '295931876', '298228734', '305034939', '311162603', '384319337', '384319338')
   -- beginning of my epoch
   and > date '2010-06-30'
   and mx.category not in ('Exclude From Mint')
   -- I have only reconciled one month of discover
-  and not (mx.account = 'Discover' and > date '2010-07-14')
+  and not (mx.account = 'Discover' and > date '2011-12-14') -- '2010-07-14'
   and not (mx.account = 'PERFORMANCE CHECKING' and > date '2011-11-16')
   -- haven't done these
   and mx.account not in ('Costco TrueEarnings Card', 'SAVINGS', 'Home Depot CC')
+  and mx.category not in ('Credit Card Payment')
 order by;
 /* Find the category split, provided it hasn't been refined yet. */