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'