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

asked 18 Apr '14, 07:54

SmartOffice's gravatar image

SmartOffice
0111
accept rate: 0%

edited 18 Apr '14, 08:27

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836

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.

(18 Apr '14, 08:22) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 18 Apr '14, 07:54

question was seen: 1,229 times

last updated: 18 Apr '14, 08:28