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.

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

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

• Entered : 5/2014
• Version : 8.6+