Sales By State Query

[Former Member]

Hello All -

We would like to develop a query that shows us monthly sales by each US state and OUS locations. For example, on the left most column, we would have each location. Then subsequent columns to the right of that would have sales for each location in Jan, Feb, March, etc.

Can anyone help with this?

Thanks,

Mike

[Former Member · Accepted solution]

That is because you have one additional comma before FROM.

[Former Member · Accepted solution]

Hi Gordon,

When i use this query NO amt came in my result ?

query reteriving all the state but no amount there....

JRAJPUT

[Former Member]

Hi Mike,

Try this one:

SELECT T0.State1 AS 'Bill-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) = YEAR(GETDATE())) AS 'JAN Amt'

FROM dbo.OCRD T0 LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode

GROUP BY T0.State1

ORDER BY T0.State1

Again, it is only take invoice amount without credit memo. Copy the 1st month carefully to get all other 11 months.

Thanks,

Gordon

[Former Member]

Thank you!

What part, specifically, do I copy and repeat?

Thanks,

Mike

[Former Member]

Just copy everything from the first comma till amt' 11 times and change to fit every month.

[Former Member]

I tried first three months below:

SELECT T0.State1 AS 'Bill-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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) AS 'MAR Amt',

FROM dbo.OCRD T0 LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode

GROUP BY T0.State1

ORDER BY T0.State1

But did not get any data -- not sure what I am doing wrong?!

Mike