Simplifying Stitch ETL’s Quickbooks Data in Redshift: Expenses 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
• Part 5: Expenses Table (You are here)
• Part 6: Revenue Table

This table summarizes all outgoing financial transactions that would fall under the “Expenses” category in a Quickbooks report.

SELECT * FROM (
SELECT 
  'Expense' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Doc Number",
  tx.txndate AS "Transaction Date",
  tx.privatenote AS "Note",
  tx.entityref__name AS "Entity Name",
  tx.entityref__type AS "Entity Type",
  txl.amount AS "Line Amount",
  txl.description AS "Line Description",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Line 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 "Doc Number",
  tx.txndate AS "Transaction Date",
  tx.privatenote AS "Note",
  tx.entityref__name AS "Entity Name",
  tx.entityref__type AS "Entity Type",
  (txl.amount * -1) AS "Line Amount",
  txl.description AS "Line Description",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Line 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 = TRUE

UNION

SELECT 
  'Bill' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Doc Number",
  tx.txndate AS "Transaction Date",
  tx.privatenote AS "Note",
  tx.vendorref__name AS "Entity Name",
  'Vendor' AS "Entity Type",
  txl.amount AS "Line Amount",
  txl.description AS "Line Description",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Line Account Name"
FROM quickbooks.quickbooks_bills tx
INNER JOIN quickbooks_shadow.bills stx ON stx.id = tx.id
LEFT JOIN quickbooks.quickbooks_bills__line txl ON tx.id = txl._sdc_source_key_id
INNER JOIN quickbooks_shadow.billlines stxl ON txl.id = stxl.id AND txl._sdc_source_key_id = stxl.parent_id
LEFT JOIN quickbooks.quickbooks_accounts acc ON txl.accountbasedexpenselinedetail__accountref__value = acc.id

UNION 

SELECT 
  'Journal Entries' AS "Transaction Type",
    cm.id AS "Transaction ID",
	cm.docnumber AS "Docnumber",
	cm.txndate AS "Transaction Date",
	sh_cml.journalentrylinedetail_postingtype AS "Note",
	sh_cml.journalentrylinedetail_entity_entityref_name AS "Entity Name",
    sh_cml.journalentrylinedetail_entity_type AS "Entity Type",
	CASE 
    WHEN sh_cml.journalentrylinedetail_postingtype = 'Credit' THEN (sh_cml.amount * -1) 
    ELSE sh_cml.amount
    END AS "Line Amount",
	sh_cml.description AS "Line Description",
	acc.acctnum || ' ' || acc.fullyqualifiedname AS "Line 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
AND acc.classification = 'Expense'

UNION

SELECT 
  'Vendor Credit' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Doc Number",
  tx.txndate AS "Transaction Date",
  tx.privatenote AS "Note",
  tx.vendorref__name AS "Entity Name",
  'Vendor' AS "Entity Type",
  (txl.amount * -1) AS "Line Amount",
  txl.description AS "Line Description",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Line Account Name"
FROM quickbooks.quickbooks_vendorcredits tx
INNER JOIN quickbooks_shadow.vendorcredits stx ON stx.id = tx.id
LEFT JOIN quickbooks.quickbooks_vendorcredits__line txl ON tx.id = txl._sdc_source_key_id
INNER JOIN quickbooks_shadow.vendorcreditlines 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

UNION

SELECT 
  'Deposit' AS "Transaction Type",
  tx.id AS "Transaction ID",
  tx.docnumber AS "Doc Number",
  tx.txndate AS "Transaction Date",
  tx.privatenote AS "Note",
  txl.depositlinedetail__entity__name AS "Entity Name",
  txl.depositlinedetail__entity__type AS "Entity Type",
  (txl.amount * -1) AS "Line Amount",
  txl.description AS "Line Description",
  acc.acctnum || ' ' || acc.fullyqualifiedname AS "Line 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
)