Identify Tax Balances in GL not Matching Tax Report

Explanation of SQL

This SQL generates a list of the taxable orders, taxable amounts, and tax rates from Sales in the GL from a particular timeframe.

You will need to adjust the date range in both queries based on the range you are studying. The results will show you the effective tax rate for all taxable sales during that time period.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL

SELECT orderID, SUM(amount) SalesAmount
FROM "GL Database"
WHERE DateTime BETWEEN "4/1/2014" AND "4/15/2014 23:59:59"
 AND Taxable = 'T'
 AND AccountCode BETWEEN 4000 AND 4999
GROUP BY orderID
ORDER BY OrderID
; TaxableOrders
 
SELECT orderID, SUM(amount) TaxAmount
FROM "GL Database"
WHERE DateTime BETWEEN "4/1/2014" AND "4/15/2014 23:59:59"
 AND AccountCode BETWEEN 2501 AND 2502
GROUP BY orderID
ORDER BY OrderID
 
; Taxes
 
SELECT T.OrderID, Tor.SalesAmount, T.TaxAmount, CAST(T.TaxAmount / tor.SalesAmount * 100 AS FLOAT) AS TaxRate
FROM TaxableOrders Tor
FULL OUTER JOIN Taxes T ON T.OrderID = Tor.OrderID
ORDER BY TaxRate

Version Information

  • Entered : 5/2014
  • Version : 8.6+

Related SQLs