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.

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

-- 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
  • Entered : 10/2010
  • Version : 4.4+
You could leave a comment if you were logged in.