Explanation of SQL
This query provides the same data used for the tax report - that is a record of all of the income and taxes records in the GL for a specified period. The dates of the report are SQL parameters that can be set during running.
This report is particularly helpful as a starting point if you are trying to compare the Avalara reports with the data in Cyrious.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- This query reconstructs the taxes from the GL. -- DECLARE @startdate datetime; DECLARE @enddate datetime; SET @startdate = '8/1/2010'; SET @enddate = '9/1/2010'; -- get all the income and join all the taxes SELECT (SELECT TaxClassName FROM TaxClass WHERE ID = COALESCE(TC1, TC2)) AS [Tax Class], COALESCE(IncomeDate, TaxDate) AS [Entry DATE], (SELECT OrderNumber FROM TransHeader WHERE ID = COALESCE(TH1, TH2)) AS [ORDER NUMBER], -- Remember to reverse the sign when displaying -- Income and Taxes because they are credits -COALESCE(IncomeNonTaxable, 0) AS [Non-Taxable Income], -COALESCE(IncomeTaxable, 0) AS [Taxable Income], -COALESCE(IncomeTotal, 0) AS [Total Income], -COALESCE(TaxTotal, 0) AS Taxes, COALESCE(TH1, TH2) AS TransHeaderID, COALESCE(TC1, TC2) AS TaxClassID FROM ( SELECT TransactionID AS TH1, TaxClassID AS TC1, SUM(CASE WHEN IsTaxable = 1 THEN Amount ELSE 0 END) AS IncomeTaxable, SUM(CASE WHEN IsTaxable = 1 THEN 0 ELSE Amount END) AS IncomeNonTaxable, SUM(Amount) AS IncomeTotal, CAST(EntryDateTime AS DATE) AS IncomeDate FROM GL WHERE GLClassificationType = 4000 AND EntryDateTime BETWEEN @startdate AND @enddate GROUP BY TransactionID, TaxClassID, CAST(EntryDateTime AS DATE) ) GLIncome FULL OUTER JOIN ( SELECT TransactionID AS TH2, TaxClassID AS TC2, SUM(Amount) AS TaxTotal, CAST(EntryDateTime AS DATE) AS TaxDate FROM GL WHERE GLClassificationType = 2005 AND EntryDateTime BETWEEN @startdate AND @enddate GROUP BY TransactionID, TaxClassID, CAST(EntryDateTime AS DATE) ) GLTaxes ON GLIncome.TH1 = GLTaxes.TH2 AND GLIncome.TC1 = GLTaxes.TC2 AND GLIncome.IncomeDate = GLTaxes.TaxDate WHERE ( abs(COALESCE(GLIncome.IncomeTaxable,0)) > 0.01 OR abs(COALESCE(GLIncome.IncomeNonTaxable,0)) > 0.01 OR abs(COALESCE(TaxTotal,0)) > 0.01 ) -- and coalesce(TC1, TC2) is not null -- exclude WriteOffs ORDER BY [Tax Class], [Entry DATE], TransHeaderID
Version Information
- Entered : 10/2010
- Version : 4.4+
You could leave a comment if you were logged in.