Invoice, freight charges and taxes monthly query by state

[Former Member]

can someone help me with a query that will give me monthly  invoice sales totals by state that will also break out the freight and tax charges

[Former Member]

yes I have, thanks a bunch !!

[kothandaraman_nagarajan]

Please close this thread.

[Former Member]

here is my query... how do I change the query to pull from the ship to inv12 instead of oinv. When I try to modify the query I'm getting an error.  declare @Year1 as int; set @Year1 = 2013; SELECT @Year1 as 'Year',  T0.State1 AS 'ship-to State',  (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 1 AND T2.State1 = T0.State1   AND YEAR(T1.DOCDATE) = @Year1) AS 'JAN Amt' , (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 2 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE)  = @Year1) AS 'FEB Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 3 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'MAR Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 4 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'APR Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 5 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'MAY Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 6 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'JUN Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 7 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'JUL Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 8 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'AUG Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 9 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'SEP Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 10 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'OCT Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 11 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'NOV Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 12 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'DEC Amt' FROM dbo.OCRD T0 LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode GROUP BY T0.State1 ORDER BY T0.State1

[Former Member]

sorry, here it is: declare @Year1 as int; set @Year1 = 2013; SELECT @Year1 as 'Year',  T0.State1 AS 'ship-to State',  (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 1 AND T2.State1 = T0.State1  AND YEAR(T1.DOCDATE) = @Year1) AS 'JAN Amt' , (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 2 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE)  = @Year1) AS 'FEB Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 3 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'MAR Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 4 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'APR Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 5 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'MAY Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 6 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'JUN Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 7 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'JUL Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 8 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'AUG Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 9 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'SEP Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 10 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'OCT Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 11 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'NOV Amt', (SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK) INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode WHERE MONTH(T1.DOCDATE) = 12 AND T2.State1 = T0.State1 AND YEAR(T1.DOCDATE) = @Year1) AS 'DEC Amt' FROM dbo.OCRD T0 LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode GROUP BY T0.State1 ORDER BY T0.State1

[kothandaraman_nagarajan]

Hi,

Have you got required query?

Thanks & Regards,

Nagarajan

[former_member465537]

hiii Felicia ,

If you check OINV  Table You Got field TotalExp That Shows You Frieght & Link OINV with INV4 that give you Tax Information

Regards,

VEER