Query to show sales by state

[Former Member]

Hi

Wondering if anyone can help me with a query. I'm trying to build a report to show all sales by state in SAP Business One. I have come across this code and while it is close to what I need it doesn't go the whole way.

Along with the sales from each state I need to include the wareshouses that stock was sold from. I also need to sum each column (I know that you can press Ctrl and Click to get totals but would like on final report). Can anyone help?

Here is the query so far:

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',
(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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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) = YEAR(GETDATE())) 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

[Breck_Carter]

This is a forum dedicated to "SQL Anywhere" which is a database product different from SAP HANA, Sybase ASE, Sybase IQ and Oracle. This is not a forum for SAP Business One, nor is it a forum for general questions about SQL (in spite of the name "SQL Anywhere" 🙂

You might have better luck asking your question on one of the SAP Business One Forums.

On the other hand, if you are using SQL Anywhere, then please show us the table layouts and maybe someone here can help.