Quickbooks
Posted By Tom Jessessky —

Simplifying Stitch ETL’s Quickbooks Data in Redshift: Revenue 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.

This is the final post in this series of queries for simplifying Quickbooks data. Note that Credit Memo’s and Refund Receipts count against Revenue, so they are negated by multiply the Line Amount field by -1 in the queries.

If you made it through this series, thank you for reading! I am aware that this is a highly specific guide, but it took me almost two weeks to create these queries, so I hope I can save someone else that time.

SELECT * FROM 
(
SELECT 
  'Credit Memo' AS "Transaction Type",
	cm.docnumber AS "Docnumber",
	cm.metadata__createtime AS "Create Time",
	cm.totalamt AS "Total Amount",
	cm.txndate AS "Transaction Date",
	cm.customermemo__value AS "Customer Memo",
	(cml.amount * -1) AS "Line Item Amount",
	cml.description AS "Description",
	cml.id AS "Line Item ID",
	cml.linenum AS "Line Num",
	cml.salesitemlinedetail_itemref_name AS "Line Item Name", 
	cml.salesitemlinedetail_qty AS "Line Item Qty", 
	cml.salesitemlinedetail_unitprice AS "Line Item Unit Price",
	cust.displayname AS "Customer Name",
	cust.parentref__value AS "Customer Parent ID",
	cust.id AS "Customer ID",
	item.incomeaccountref__name AS "Item Account Name"
FROM quickbooks.quickbooks_creditmemos cm
INNER JOIN quickbooks_shadow.creditmemos sh_cm ON cm.id = sh_cm.id
LEFT JOIN quickbooks_shadow.creditmemolines cml ON cml.parent_id = sh_cm.id
LEFT JOIN quickbooks.quickbooks_customers cust ON cust.id = cm.customerref__value
LEFT JOIN quickbooks.quickbooks_items item ON item.id = cml.salesitemlinedetail_itemref_value
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = item.incomeaccountref__value

UNION

SELECT 
  'Refund Receipt' AS "Transaction Type",
	cm.docnumber AS "Docnumber",
	cm.metadata__createtime AS "Create Time",
	cm.totalamt AS "Total Amount",
	cm.txndate AS "Transaction Date",
	'' AS "Customer Memo",
	(cml.amount * -1) AS "Line Item Amount",
	cml.description AS "Description",
	cml.id AS "Line Item ID",
	cml.linenum AS "Line Num",
	cml.salesitemlinedetail_itemref_name AS "Line Item Name", 
	cml.salesitemlinedetail_qty AS "Line Item Qty", 
	cml.salesitemlinedetail_unitprice AS "Line Item Unit Price",
	cust.displayname AS "Customer Name",
	cust.parentref__value AS "Customer Parent ID",
	cust.id AS "Customer ID",
	item.incomeaccountref__name AS "Item Account Name"
FROM quickbooks.quickbooks_refundreceipts cm
INNER JOIN quickbooks_shadow.refundreceipts sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks_shadow.refundreceiptlines cml ON cml.parent_id = sh_cm.id
LEFT JOIN quickbooks.quickbooks_customers cust ON cust.id = cm.customerref__value
LEFT JOIN quickbooks.quickbooks_items item ON item.id = cml.salesitemlinedetail_itemref_value
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = item.incomeaccountref__value

UNION

SELECT 
  'Sales Receipt' AS "Transaction Type",
	cm.docnumber AS "Docnumber",
	cm.metadata__createtime AS "Create Time",
	cm.totalamt AS "Total Amount",
	cm.txndate AS "Transaction Date",
	cm.customermemo__value AS "Customer Memo",
	cml.amount AS "Line Item Amount",
	cml.description AS "Description",
	cml.id AS "Line Item ID",
	cml.linenum AS "Line Num",
	cml.salesitemlinedetail_itemref_name AS "Line Item Name", 
	cml.salesitemlinedetail_qty AS "Line Item Qty", 
	cml.salesitemlinedetail_unitprice AS "Line Item Unit Price",
	cust.displayname AS "Customer Name",
	cust.parentref__value AS "Customer Parent ID",
	cust.id AS "Customer ID",
	item.incomeaccountref__name AS "Item Account Name"
FROM quickbooks.quickbooks_salesreceipts cm
INNER JOIN quickbooks_shadow.salesreceipts sh_cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks_shadow.salesreceiptlines cml ON cml.parent_id = sh_cm.id
LEFT JOIN quickbooks.quickbooks_customers cust ON cust.id = cm.customerref__value
LEFT JOIN quickbooks.quickbooks_items item ON item.id = cml.salesitemlinedetail_itemref_value
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = item.incomeaccountref__value

UNION

SELECT 
  'Journal Entries' AS "Transaction Type",
	cm.docnumber AS "Docnumber",
	cm.metadata__createtime AS "Create Time",
	0 AS "Total Amount",
	cm.txndate AS "Transaction Date",
	sh_cml.journalentrylinedetail_postingtype AS "Customer Memo",
	CASE 
    WHEN sh_cml.journalentrylinedetail_postingtype = 'Credit' THEN sh_cml.amount
    ELSE (sh_cml.amount * -1) 
    END AS "Line Item Amount",
	sh_cml.description AS "Description",
	sh_cml.id AS "Line Item ID",
	0 AS "Line Num",
	'' AS "Line Item Name", 
	0 AS "Line Item Qty", 
	0 AS "Line Item Unit Price",
	cust.displayname AS "Customer Name",
	cust.parentref__value AS "Customer Parent ID",
	cust.id AS "Customer ID",
	sh_cml.journalentrylinedetail_accountref_name AS "Item 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_customers cust ON (cust.id = sh_cml.journalentrylinedetail_entity_entityref_value AND sh_cml.journalentrylinedetail_entity_type = 'Customer')
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = sh_cml.journalentrylinedetail_accountref_value
WHERE sh_cml.amount IS NOT null

UNION

SELECT 
  'Invoice' AS "Transaction Type",
	cm.docnumber AS "Docnumber",
	cm.metadata__createtime AS "Create Time",
	cm.totalamt AS "Total Amount",
	cm.txndate AS "Transaction Date",
	cm.customermemo__value AS "Customer Memo",
	cml.amount AS "Line Item Amount",
	cml.description AS "Description",
	cml.id AS "Line Item ID",
	cml.linenum AS "Line Num",
	cml.salesitemlinedetail_itemref_name AS "Line Item Name", 
	cml.salesitemlinedetail_qty AS "Line Item Qty", 
	cml.salesitemlinedetail_unitprice AS "Line Item Unit Price",
	cust.displayname AS "Customer Name",
	cust.parentref__value AS "Customer Parent ID",
	cust.id AS "Customer ID",
	item.incomeaccountref__name AS "Item Account Name"
FROM quickbooks.quickbooks_invoices cm
INNER JOIN quickbooks_shadow.invoices sh_cm ON sh_cm.id = cm.id 
LEFT JOIN quickbooks_shadow.invoicelines cml ON cml.parent_id = sh_cm.id
LEFT JOIN quickbooks.quickbooks_customers cust ON cust.id = cm.customerref__value
LEFT JOIN quickbooks.quickbooks_items item ON item.id = cml.salesitemlinedetail_itemref_value
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = item.incomeaccountref__value

UNION

SELECT 
  'Deposits' AS "Transaction Type",
	cm.docnumber AS "Docnumber",
	cm.metadata__createtime AS "Create Time",
	0 AS "Total Amount",
	cm.txndate AS "Transaction Date",
	sh_cml.depositlinedetail_postingtype AS "Customer Memo",
	sh_cml.amount AS "Line Item Amount",
	sh_cml.description AS "Description",
	sh_cml.id AS "Line Item ID",
	0 AS "Line Num",
	'' AS "Line Item Name", 
	0 AS "Line Item Qty", 
	0 AS "Line Item Unit Price",
	cust.displayname AS "Customer Name",
	cust.parentref__value AS "Customer Parent ID",
	cust.id AS "Customer ID",
	sh_cml.depositlinedetail_accountref_name AS "Item Account Name"
FROM quickbooks_shadow.deposits sh_cm
INNER JOIN quickbooks.quickbooks_deposits cm ON sh_cm.id = cm.id
LEFT JOIN quickbooks_shadow.depositlines sh_cml ON sh_cml.parent_id = cm.id
LEFT JOIN quickbooks.quickbooks_customers cust ON (cust.id = sh_cml.depositlinedetail_entity_entityref_value AND sh_cml.depositlinedetail_entity_type = 'Customer')
LEFT JOIN quickbooks.quickbooks_accounts acc ON acc.id = sh_cml.depositlinedetail_accountref_value
WHERE sh_cml.amount IS NOT null
)

I'm the Analytics Therapist at Redox, a quickly growing technology platform that enables organizations to send healthcare data back and forth. Here, I write about our journey to become a data-driven organization, and the technical challenges I've faced along the way. All views and opinions are my own and do not represent those of my employer.

View Comments
There are currently no comments.