Simplifying Stitch ETL’s Quickbooks Data in Redshift: Cash Movements Table

This is the first post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema
• Part 3: Budgets Table
• Part 4: Cash Movements Table (You are here)
• Part 5: Expenses Table
• Part 6: Revenue Table

This is the Cash Movements table, and the first of the complex tables in this series. It joins many tables together into a single table using JOIN’s and UNION’s.

Notice the differentiation between “In” and “Out” transactions – one will multiply the amount by -1 to show that it is negative cashflow.

SELECT * FROM (
SELECT 
  'Deposit In' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Docnumber",
  tx.totalamt AS "Total Amount",
  tx.txndate AS "Transaction Date",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_deposits tx
INNER JOIN quickbooks_shadow.deposits stx ON stx.id = tx.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = tx.deposittoaccountref__value

UNION

SELECT 
  'Deposit Out' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Docnumber",
  (txl.amount * -1) AS "Total Amount",
  tx.txndate AS "Transaction Date",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_deposits tx
INNER JOIN quickbooks_shadow.deposits stx ON stx.id = tx.id
LEFT JOIN quickbooks.quickbooks_deposits__line txl ON tx.id = txl._sdc_source_key_id
INNER JOIN quickbooks_shadow.depositlines stxl ON txl.id = stxl.id AND txl._sdc_source_key_id = stxl.parent_id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = txl.depositlinedetail__accountref__value

UNION

SELECT 
  'Journal Entries' AS "Transaction Type",
  cm.id AS "Transaction ID",
	cm.docnumber AS "Docnumber",
	CASE 
    WHEN sh_cml.journalentrylinedetail_postingtype = 'Credit' THEN (sh_cml.amount * -1) 
    ELSE sh_cml.amount
    END AS "Total Amount",
	cm.txndate AS "Transaction Date",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks_shadow.journalentries sh_cm
INNER JOIN quickbooks.quickbooks_journalentries cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks_shadow.journalentrylines sh_cml ON sh_cml.parent_id = cm.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = sh_cml.journalentrylinedetail_accountref_value
WHERE sh_cml.amount IS NOT NULL

UNION

SELECT 
  'Expense In' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Docnumber",
  (tx.totalamt * -1) as "Total Amount",
  tx.txndate AS "Transaction Date",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_purchases tx
INNER JOIN quickbooks_shadow.purchases stx ON stx.id = tx.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = tx.accountref__value
WHERE tx.credit IS NOT TRUE

UNION

SELECT 
  'Expense Out' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Docnumber",
  txl.amount as "Total Amount",
  tx.txndate AS "Transaction Date",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_purchases tx
INNER JOIN quickbooks_shadow.purchases stx ON stx.id = tx.id
LEFT JOIN quickbooks.quickbooks_purchases__line txl ON tx.id = txl._sdc_source_key_id
INNER JOIN quickbooks_shadow.purchaselines stxl ON txl.id = stxl.id AND txl._sdc_source_key_id = stxl.parent_id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = txl.accountbasedexpenselinedetail__accountref__value
WHERE tx.credit IS NOT TRUE

UNION

SELECT 
  'Credit Card Credit' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Docnumber",
  (txl.amount * -1) as "Total Amount",
  tx.txndate AS "Transaction Date",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_purchases tx
INNER JOIN quickbooks_shadow.purchases stx ON stx.id = tx.id
LEFT JOIN quickbooks.quickbooks_purchases__line txl ON tx.id = txl._sdc_source_key_id
INNER JOIN quickbooks_shadow.purchaselines stxl ON txl.id = stxl.id AND txl._sdc_source_key_id = stxl.parent_id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = txl.accountbasedexpenselinedetail__accountref__value
WHERE tx.credit IS TRUE

UNION

SELECT 
  'Sales Receipt' AS "Transaction Type",
	cm.id AS "Transaction ID",
	cm.docnumber AS "Docnumber",
	cm.totalamt AS "Total Amount",
	cm.txndate AS "Transaction Date",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_salesreceipts cm
INNER JOIN quickbooks_shadow.salesreceipts sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = cm.deposittoaccountref__value

UNION

SELECT 
  'Refund Receipt' AS "Transaction Type",
  cm.id AS "Transaction ID",
	cm.docnumber AS "Docnumber",
	(cm.totalamt * -1) AS "Total Amount",
	cm.txndate AS "Transaction Date",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_refundreceipts cm
INNER JOIN quickbooks_shadow.refundreceipts sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = cm.deposittoaccountref__value

UNION

SELECT 
  'Payment' AS "Transaction Type",
  cm.id AS "Transaction ID",
	'' AS "Docnumber",
	cm.totalamt AS "Total Amount",
	cm.txndate AS "Transaction Date",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_payments cm
INNER JOIN quickbooks_shadow.payments sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = cm.deposittoaccountref__value

UNION

SELECT 
  'Bill Payment' AS "Transaction Type",
  cm.id AS "Transaction ID",
	cm.docnumber AS "Docnumber",
	(cm.totalamt * -1) AS "Total Amount",
	cm.txndate AS "Transaction Date",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_billpayments cm
INNER JOIN quickbooks_shadow.billpayments sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = cm.checkpayment__bankaccountref__value

UNION

SELECT 
  'Transfer Out' AS "Transaction Type",
  cm.id AS "Transaction ID",
	'' AS "Docnumber",
	(cm.amount * -1) AS "Total Amount",
	cm.txndate AS "Transaction Date",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_transfers cm
-- INNER JOIN quickbooks_shadow.transfers sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = cm.fromaccountref__value

UNION

SELECT 
  'Transfer In' AS "Transaction Type",
  cm.id AS "Transaction ID",
	'' AS "Docnumber",
	cm.amount AS "Total Amount",
	cm.txndate AS "Transaction Date",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Account Name"
FROM quickbooks.quickbooks_transfers cm
-- INNER JOIN quickbooks_shadow.transfers sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = cm.toaccountref__value
)