Explanation of SQL
This report pulls the data that is returned from Avalara and stored in the line item record. It formats this into a report that may be helpful for comparing to Avalara.
WARNING: This report should NOT be used for paying taxes. While it may be helpful for checking, it does NOT pull data from the GL. Therefore, it does NOT properly handle any adjustments to orders made after they are a sale. For example, an order that is marked sale in one month but voided in another will never have the taxes reduced for the void!
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
DECLARE @startdate datetime; DECLARE @enddate datetime; SET @startdate = '6/1/2010'; SET @enddate = '7/1/2010'; SELECT OrderNumber, (SELECT CompanyName FROM Account WHERE ID = AccountID) AS Company, (SELECT Description FROM TransHeader WHERE ID = TransHeaderID) AS Description, LineItemNumber, Taxes.value('(TaxRate/text())[1]', 'float') AS TaxRate, Taxes.value('(TaxAmount/text())[1]', 'money') AS TaxAmount, Taxes.value('(TaxCode/text())[1]', 'varchar(25)') AS TaxCode, SubTotalPrice, TaxesPrice, TransHeaderID, TransDetailID, AccountID, (CASE WHEN TaxesPrice = 0 THEN 0 ELSE (Taxes.value('(TaxAmount/text())[1]', 'money') / TaxesPrice) END) AS PercentOfTaxes FROM ( SELECT TH.OrderNumber, TD.LineItemNumber, TD.SubTotalPrice, TD.TaxesPrice, TH.ID AS TransHeaderID, TD.ID AS TransDetailID, TH.AccountID, CAST(TD.TaxItems AS XML) AS TaxXML FROM TransDetail TD JOIN TransHeader TH ON TD.TransHeaderID = TH.ID WHERE TH.SaleDate BETWEEN @startdate AND @enddate ) Temp -- Cross Join this with a row for each ID in the Variable CROSS APPLY TaxXML.nodes('//Tax') AS VARIABLES(Taxes) ORDER BY TransHeaderID, LineItemNumber
Version Information
- Entered : 8/2010
- Version : 4.0+
You could leave a comment if you were logged in.