Fetching Transactions

When companies reinvent their own accounting software, they usually start from the (reasonable) position of just mirroring basic accounting processes. You have transactions, for an amount, and then tagged with information about what the transaction actually represents. So, for example, if you wanted to find all the transactions which represent tax paid, you'd need to filter on some metadata and then sum up the amounts.

It quickly gets more complicated. In some organizations, that complexity keeps growing, as it turns out that each department uses slightly different codes, the rules change over time, this central accounting database gradually eats older databases which had wildly different rules. Before long, you end up with a database so krufty that it's a miracle SQL Server doesn't just up and quit.

That's the situation Giles W found himself in. What follows is an example query, which exists to answer the simple question: on 20th December, 2013, how much tax was paid across all transactions? For most database designs, that might be an expensive query, but hopefully a simple query. For this one… well… they may need to do some redesigning. Note the horizontal scrolling on today's code, there was no good place for me to add linebreaks for readability.

SELECT SUM(Tax_Paid) AS Tax_Paid FROM ( SELECT SUM(te.tax1_amount + te.tax2_amount) AS Tax_Paid FROM transactions t WITH (NOLOCK) LEFT JOIN transaction_ext te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id WHERE t.Start_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Start_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.Reversed <> 1 AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.transaction_type in ('NPROD','NCRADJ','PROD','CRADJ','NEW','TAXEXEM','COUPON','COUPONS','TAXFREE') UNION ALL SELECT SUM((te.tax1_amount + te.tax2_amount) * t.quantity) AS Tax_Paid FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.transaction_type in ('SCOFFER','SCPRIV','C_SALE','COUPON','COUPONS','TAXEXEM','TAXFREE') UNION ALL SELECT SUM(te.tax1_amount + te.tax2_amount) AS Tax_Paid FROM transactions t WITH (NOLOCK) LEFT JOIN transaction_ext te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id LEFT JOIN tab_accounts ta WITH (NOLOCK) on te.sale_invoice_no = ta.sale_invoice_no and ta.location_id = ta.location_id WHERE t.Start_Date <= '2013-12-20' AND t.Start_Time <= '2013-12-20 06:00:00' AND t.Reversed <> 1 AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.transaction_type in ('NPROD','NCRADJ','PROD','CRADJ','NEW','TAXEXEM','COUPON','COUPONS','TAXFREE') AND ta.payment_cancelled <> 1 AND te.group_sale_no in (SELECT sp.group_sale_no FROM Split_Payments sp WITH (NOLOCK) INNER JOIN Sale_Invoices si WITH (NOLOCK) on sp.location_id = si.location_id and sp.group_sale_no = si.invoice_no WHERE sp.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND si.date_issued < '2013-12-20 06:00:00' AND sp.location_id = 40123 AND sp.Sublocation = 0 AND Transaction_Amount >= 0 GROUP BY sp.group_sale_no) UNION ALL SELECT SUM((te.tax1_amount + te.tax2_amount)* t.quantity) AS Tax_Paid FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id LEFT JOIN tab_accounts ta WITH (NOLOCK) on te.sale_invoice_no = ta.sale_invoice_no and ta.location_id = ta.location_id WHERE t.Transaction_Date <= '2013-12-20' AND t.Transaction_Time <= '2013-12-20 06:00:00' AND t.location_id = 40123 AND t.sublocation = 0 AND t.Cashier > '' AND t.transaction_type in ('SCOFFER','SCPRIV','C_SALE','COUPON','COUPONS','TAXEXEM','TAXFREE') AND ta.payment_cancelled <> 1 AND te.group_sale_no in (SELECT sp.group_sale_no FROM Split_Payments sp WITH (NOLOCK) INNER JOIN Sale_Invoices si on sp.location_id = si.location_id and sp.group_sale_no = si.invoice_no WHERE sp.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND si.date_issued < '2013-12-20 06:00:00' AND sp.location_id = 40123 AND sp.Sublocation = 0 AND Transaction_Amount >= 0 GROUP BY sp.group_sale_no) UNION ALL SELECT -1 * SUM(te.tax1_amount + te.tax2_amount) AS Tax_Paid FROM transactions t WITH (NOLOCK) LEFT JOIN transaction_ext te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id LEFT JOIN (SELECT transaction_amount=sum(transaction_amount), transaction_time=min(transaction_time), location_id, group_sale_no FROM Split_Payments GROUP BY location_id, group_sale_no) as sp on sp.group_sale_no = te.group_sale_no and sp.location_id = te.location_id WHERE t.Start_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Start_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.Reversed <> 1 AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.payment_type = 222 AND t.transaction_type in ('NPROD','NCRADJ','PROD','CRADJ','NEW','TAXEXEM','COUPON','COUPONS','TAXFREE') AND (sp.group_sale_no is NULL OR (sp.transaction_time > '2013-12-21 05:59:59' AND sp.transaction_amount >= 0)) UNION ALL SELECT -1 * SUM((te.tax1_amount + te.tax2_amount) * t.quantity) AS Tax_Paid FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id LEFT JOIN (SELECT transaction_amount=sum(transaction_amount), transaction_time=min(transaction_time), location_id, group_sale_no FROM Split_Payments GROUP BY location_id, group_sale_no) as sp on sp.group_sale_no = te.group_sale_no and sp.location_id = te.location_id WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.payment_type = 222 AND (t.transaction_type in ('SCOFFER','SCPRIV','C_SALE','COUPON','COUPONS','TAXEXEM','TAXFREE') OR t.transaction_type = 'DEPOSIT' and (te.comment IS NULL or te.comment = '')) AND (sp.group_sale_no is NULL OR (sp.transaction_time > '2013-12-21 05:59:59' AND sp.transaction_amount >= 0)) UNION ALL SELECT sum((te.tax1_amount + te.tax2_amount) * t.quantity) as Tax_Paid FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id INNER JOIN (SELECT distinct te.sale_invoice_no FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and
t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id LEFT JOIN (SELECT transaction_amount=sum(transaction_amount), transaction_time=min(transaction_time), location_id,
group_sale_no FROM Split_Payments GROUP BY location_id, group_sale_no) as sp on sp.group_sale_no = te.group_sale_no and sp.location_id = te.location_id WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00'
AND '2013-12-21 05:59:59' AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.payment_type = 222 AND t.transaction_type = 'CANCTAB' AND (sp.group_sale_no is NULL OR (sp.transaction_time > '2013-12-21 05:59:59' AND sp.transaction_amount >= 0)) ) s ON
te.sale_invoice_no = s.sale_invoice_no WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.transaction_type <> 'CANCTAB' AND t.location_id = 40123 AND t.Sublocation = 0 AND
t.Cashier > '' UNION ALL SELECT sum(te.tax1_amount + te.tax2_amount) as Tax_Paid FROM transactions t WITH (NOLOCK) LEFT JOIN transaction_ext te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id INNER JOIN (SELECT distinct
te.sale_invoice_no FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and
te.location_id = si.location_id LEFT JOIN (SELECT transaction_amount=sum(transaction_amount), transaction_time=min(transaction_time), location_id, group_sale_no FROM Split_Payments GROUP BY location_id, group_sale_no) as sp on sp.group_sale_no = te.group_sale_no and
sp.location_id = te.location_id WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.payment_type = 222 AND t.transaction_type = 'CANCTAB' AND (sp.group_sale_no is NULL OR (sp.transaction_time > '2013-12-21 05:59:59' AND sp.transaction_amount >= 0)) ) s ON te.sale_invoice_no = s.sale_invoice_no WHERE t.Start_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Start_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.Reversed <> 1 AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' UNION ALL SELECT SUM((te.tax1_amount + te.tax2_amount) * t.quantity) AS Tax_Paid FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.transaction_type = 'REFUND' UNION ALL SELECT SUM((te.tax1_amount + te.tax2_amount) * t.quantity) AS Tax_Paid FROM other_transactions t WITH (NOLOCK) LEFT JOIN ot_extention te WITH (NOLOCK) on t.transaction_id = te.transaction_id and t.location_id = te.location_id LEFT JOIN sale_invoices si WITH (NOLOCK) on te.sale_invoice_no = si.invoice_no and te.location_id = si.location_id WHERE t.Transaction_Date BETWEEN '2013-12-20' AND '2013-12-21' AND t.Transaction_Time BETWEEN '2013-12-20 06:00:00' AND '2013-12-21 05:59:59' AND t.location_id = 40123 AND t.Sublocation = 0 AND t.Cashier > '' AND t.transaction_type = 'REVADJ' ) AS Taxes

"This," Giles writes, "is what you would see if you wanted to tinker with the query to account for some new transaction type. Presumably shortly before resigning."

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!