cancel
Showing results for 
Search instead for 
Did you mean: 

Sales By Style

Former Member
0 Kudos

Hello -

We currently use the query below that shows us sales by stocking unit per month.

However, we would like to have a summary view that just shows us the sales by Style # each month. The Style # is the first four characters in our nine digit stocking number. The two characters after that is the color and the three after that is for the size. We are a clothing company.

How can we modify or create new query to do this?

Thanks!

Mike

SELECT T0.ITEMCODE,

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV Amt',

(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with

(NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE

= T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC Amt'

FROM dbo.OITM T0

LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode

WHERE T0.SellItem = 'Y'

GROUP BY T0.ItemCode,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

ORDER BY T0. ITEMCODE

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Mike,

Here you are:

SELECT LEFT(T0.ITEMCODE,4), SUM(T0.QUANTITY*T0.PRICE) AS 'Current Amt'

FROM INV1 T0

WHERE MONTH(T0.DOCDATE) = MONTH(GETDATE()) AND YEAR(T0.DOCDATE) = YEAR(GETDATE()) AND T0.ItemCode IS NOT NULL

GROUP BY LEFT(T0.ITEMCODE,4)

ORDER BY LEFT(T0.ITEMCODE,4)

Thanks,

Gordon

Former Member
0 Kudos

Wonderful!

Can we show sales by month for each of these items, Jan-Dec 2010?

Thanks!!

Mike

Former Member
0 Kudos

Here u r:

SELECT LEFT(T0.ITEMCODE,4) AS 'Style', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV Amt', (SELECT SUM(T1.QUANTITYT1.PRICE) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC Amt'

FROM dbo.OITM T0 LEFT JOIN dbo.INV1 T1 ON LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) WHERE T0.SellItem = 'Y' GROUP BY LEFT(T0.ITEMCODE,4),YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

ORDER BY LEFT(T0.ITEMCODE,4)

zal_parchem2
Active Contributor
0 Kudos

Hello Mike and Gordon...

Just a short question. I have just been asked to write a similar report for sales and, having the financial background that I do, I attempt to make sure that the sales dollars reflect the invoices and the credit memos so that the SAP B1 yearly (or monthly) sales on the Sales Analysis results equal the SQL results. From my experience, sales equals debits and credits to Sales Revenue. However, in most forum threads, the vast majority of folks concentrate only on the OINV invoice table and do nothing with the ORIN credit memo table.

Why is that? I know it makes the SQL a bit more lengthy and all, but is it that customers are just comfortable with a "ball park" dollar amount? Do they consider credit memos as an "inconsequential variance" when looking at the results? Doesn't someone inevitably come back and ask why SQL and SAP B1 do not balance or equal?

I know this has nothing to do with resolving your question, Mike, but the seldom-used ORIN dollars is just something that puzzles me (when I have the time to jump on the forum between gigs - like now) and would like to hear your cut on that since you are not including ORIN. And Gordon, you have so much experience here it would be interesting to hear what you think about this...

Thanks and regards - Zal

Former Member
0 Kudos

Hello Zal,

I am fully aware the ORIN table is not included in the query.

However, it is always depends. If Credit Memo total is less than certain percentage, it should be fine for this report. After all, it is only a rough idea for sales. It is not used for P&L

To include RIN1 in this complicated query may not be practicable. Whoever use this query should know it. It is easy to create an identical structure query with RIN tables instead if Credit amounts are significant.

Thanks,

Gordon

Former Member
0 Kudos

Hi Zal -

Returns are a small percentage of our total sales and a ballpark analysis is sufficient for us on this query - at least for now.

Thanks,

Mike

Answers (0)