Skip to Content
0
Former Member
Jun 08, 2011 at 08:07 PM

Tax balance by State

29 Views

GURU's

I am trying to write this query that will give me the balance by state of the taxes ( Thanks to Gordon Du for the start). Here is what I have, but the numbers surely do not work out correctly. Anyone think they can take a look and tell me where my calculations may be off at?

Here is the query I am using:

SELECT T0.State1 AS 'Bill-to State',

(SELECT SUM(T1.DocTotal * (100 - T1.DiscPrcnt) / 100) + CASE WHEN SUM(T1.VATSum) != 0 THEN SUM(T1.VATSum) ELSE 0 END AS Expr1

FROM OINV AS T1 WITH (NOLOCK) INNER JOIN

OCRD AS T2 ON T2.CardCode = T1.CardCode

WHERE (T2.State1 = T0.State1)) AS 'Invc Amt',

(SELECT SUM(T1.DocTotal * (100 - T1.DiscPrcnt) / 100) AS Expr1

FROM OINV AS T1 WITH (NOLOCK) INNER JOIN

OCRD AS T2 ON T2.CardCode = T1.CardCode

WHERE (T2.State1 = T0.State1)) AS 'Sales Amt',

(SELECT SUM(T1.DocTotal * (100 - T1.DiscPrcnt) / 100) AS Expr1

FROM OINV AS T1 WITH (NOLOCK) INNER JOIN

OCRD AS T2 ON T2.CardCode = T1.CardCode INNER JOIN

INV1 ON T1.DocEntry = INV1.DocEntry

WHERE (T2.State1 = T0.State1) AND (INV1.TaxCode LIKE '%EX')) AS 'XMPT Amt',

(SELECT SUM(T1.DocTotal * (100 - T1.DiscPrcnt) / 100) AS Expr1

FROM OINV AS T1 WITH (NOLOCK) INNER JOIN

OCRD AS T2 ON T2.CardCode = T1.CardCode INNER JOIN

INV1 AS INV1_1 ON T1.DocEntry = INV1_1.DocEntry

WHERE (T2.State1 = T0.State1) AND (INV1_1.TaxCode NOT LIKE '%EX') AND (INV1_1.VatPrcnt = 0)) AS 'NOT Taxed',

(SELECT SUM(T1.DocTotal * (100 - T1.DiscPrcnt) / 100) AS Expr1

FROM OINV AS T1 WITH (NOLOCK) INNER JOIN

OCRD AS T2 ON T2.CardCode = T1.CardCode INNER JOIN

INV1 AS INV1_1 ON T1.DocEntry = INV1_1.DocEntry

WHERE (T2.State1 = T0.State1) AND (INV1_1.VatSum = 0)) AS 'Tax Base',

(SELECT SUM(T1.VatSum) AS Expr1

FROM OINV AS T1 WITH (NOLOCK) INNER JOIN

OCRD AS T2 ON T2.CardCode = T1.CardCode

WHERE (T2.State1 = T0.State1)) AS 'Tax'

FROM OCRD AS T0 LEFT OUTER JOIN

OINV AS T1 ON T1.CardCode = T0.CardCode

GROUP BY T0.State1

ORDER BY 'Bill-to State'