August 11, 2011
I had a request from a blog reader for a view that shows sales of items by year. This seemed like pretty useful code to create, so I put the following view together. I am making a few assumptions (listed in the view comments), and am hard coding years from 2005 through 2011. You can easily change the years or add new ones by following the example in my code.
~~~~~
create view view_Sales_by_Item_by_Year
as
/******************************************************************
view_Sales_by_Item_by_Year
Created Aug 11, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
- Returns total sales (invoices - returns) for each item by year
- Only posted invoices and returns are included
- Voided transations are excluded
- Item Description is taken from Inventory Item Maintenance
for all inventory items,
and from SOP line items for non-inventory items
******************************************************************/
SELECT
D.ITEMNMBR [Item_Number],
D.Item_Description,
sum(case when year(D.DOCDATE) = 2005
then D.SALES else 0 end) as [2005 Sales],
sum(case when year(D.DOCDATE) = 2006
then D.SALES else 0 end) as [2006 Sales],
sum(case when year(D.DOCDATE) = 2007
then D.SALES else 0 end) as [2007 Sales],
sum(case when year(D.DOCDATE) = 2008
then D.SALES else 0 end) as [2008 Sales],
sum(case when year(D.DOCDATE) = 2009
then D.SALES else 0 end) as [2009 Sales],
sum(case when year(D.DOCDATE) = 2010
then D.SALES else 0 end) as [2010 Sales],
sum(case when year(D.DOCDATE) = 2011
then D.SALES else 0 end) as [2011 Sales]
FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
CASE SD.SOPTYPE
WHEN 3 THEN SD.XTNDPRCE
WHEN 4 THEN SD.XTNDPRCE*-1
END SALES
FROM SOP30200 SH
INNER JOIN
SOP30300 SD
ON SD.SOPNUMBE = SH.SOPNUMBE
AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
IV00101 I
ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0
AND SH.SOPTYPE IN (3,4)
AND SD.XTNDPRCE <> 0
AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') D
GROUP BY D.ITEMNMBR, D.Item_Description
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Sales_by_Item_by_Year TO DYNGRP
~~~~~
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria -
If I wanted to look at customers and vendors in the same manner, what is the SQL code? I have been trying with no luck – I can only get individual rows with customers/vendors and their annual sales but would prefer to see my years in columns.
Mark,
The trick to getting the data into yearly columns is to use the “sum” code, as in the example above. Give me a few days and I will publish some new posts to help with this.
-Victoria
How about the same but for customers? I’ve been trying to figure out how and which table to pull from but have not been able to figure out (dollar value).
Thanks!!!
Hi Mark,
The inventory sales are coming from the SOP (Sales Order Processing) module. I can change this easily enough to do it by customer…but then a few questions then come up:
-Victoria
I had not considered your points. Could RM and COP be combined?
I would be looking at subtotal not invoice amount. The invoice amount would give freight, tax, miscellaneous.
Mark,
You cannot really ‘combine’ RM and SOP, as SOP is a subset of RM. So if you want information from both, you would most likely be better off simply looking at RM data.
-Victoria
Thanks a lot Victoria I tested and it works perfect! If you need any help related to network and Windows Servers let me know to pay it forward! =D
- George
Hi Victoria, i need this but instead of year by month, what do i need to change?
Thanks,
George
Hi George,
I just posted a new view for this: http://victoriayudin.com/2011/08/29/sql-view-for-sales-by-item-by-month/.
-Victoria
Victoria-
Once again you continue to astound me with your knowledge. Wat part of this can I change to get it to reflect number of units (or quantity of item) rather than dollars? Dollars are good, but as prices change, the dollars can skew whether it is worthwhile to carry an item.
I’m blushing that you actually chose my idea to use for a blog topic.
Mark
Mark,
Thanks for the great idea! You know, I was actually wondering whether to do this for dollars or quantities when I was writing it.
You can change this to quantities instead by changing all instances you find of
SD.XTNDPRCE(there should be 3 of them) to beSD.QUANTITYinstead.-Victoria
PS, this will not take into consideraton Units of Measure…if you need that, let me know and I can take a look at it further.
-Victoria
Great idea !!! I am getting this error though trying to create it
Msg 102, Level 15, State 1, Procedure view_Sales_by_Item_by_Year, Line 26
Incorrect syntax near ‘=’.
Msg 102, Level 15, State 1, Procedure view_Sales_by_Item_by_Year, Line 50
Incorrect syntax near ‘D’.
Hi Vic,
I had a stray bracket…can you please try it again?
Thanks,
-Victoria
Perfect Worked like a charm!!! Another useful view/report by the Queen of GP reporting!!!
Thanks!
Awww! Thanks for testing it so quickly.
Have a great day,
-Victoria