SQL view for sales by item by year


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 https://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
-- Updated Jul 12, 2012 to add Total Sales, 2012 and change field names
-- *********************************************************************

SELECT
D.ITEMNMBR Item_Number,
D.Item_Description,
sum(case when year(D.DOCDATE) = 2005 then D.SALES else 0 end) as Sales_in_2005,
sum(case when year(D.DOCDATE) = 2006 then D.SALES else 0 end) as Sales_in_2006,
sum(case when year(D.DOCDATE) = 2007 then D.SALES else 0 end) as Sales_in_2007,
sum(case when year(D.DOCDATE) = 2008 then D.SALES else 0 end) as Sales_in_2008,
sum(case when year(D.DOCDATE) = 2009 then D.SALES else 0 end) as Sales_in_2009,
sum(case when year(D.DOCDATE) = 2010 then D.SALES else 0 end) as Sales_in_2010,
sum(case when year(D.DOCDATE) = 2011 then D.SALES else 0 end) as Sales_in_2011,
sum(case when year(D.DOCDATE) = 2012 then D.SALES else 0 end) as Sales_in_2012,
sum(D.SALES) Total_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.

33 Responses to “SQL view for sales by item by year”

  1. Hi Victoria. I wrote all this in the SQL, it ran successfully, but I can’t ‘find’ it…
    I’m not very advanced, so please forgive my stupidity…

    Like

    • Hi Robin,

      What are you trying to do? If you just want to run the view in SQL to see the results, you can use the following query:

      select * from view_Sales_by_Item_by_Year
      

      If you want to use this to create a SmartList, take a look at this blog post.

      If you’re looking to do something else, please give me some details.

      -Victoria

      Like

  2. This report is EXACTLY what I need! However, I don’t know SQL, and am just learning Smartlist Builder. Can you show me what a calculated field would look like for one of the years? I could figure the rest out myself from that, just having trouble with calculating dates. Wish I were as smart as you!! Thanks.

    Like

    • Hi Sharon,

      I don’t recommend doing this in SmartList Builder directly, it would be no less complicated than doing it in SQL and frankly, I am not sure you can duplicate everything I am doing here in SmartList Builder. The beauty of using SQL for this is that I have already done the work for you. I also have a blog post here showing how to use a SQL view with SmartList Builder. Hopefully this will be all you need.

      -Victoria

      Like

  3. Victoria

    I have used this view for management and they like it but the wanted to see only the last four years. My issues is when I choose individual columns not “*” I get the year in the sales total quantity (see below). What am I doing wrong? I use Server Management Studio and I have both written and dragged and drop with equal results. Also management wants to add current on hand quantity which I plan to link by item number to iv00102 any suggestions?

    Patrick

    Item_Number Item_Description _Sales _Sales
    61.40.510.01616 INT NiTi AT L .016x.016/.41x.41 2005 2006
    37.30.258.00020 MB Oradon RL 22-802 20 LG 2005 2006
    37.30.158.00003 MB Oradon RU 22-852 03 LG 2005 2006

    Like

    • Hi Patrick,

      I changed the view slightly to put it in the new format with line numbers…that makes it easier to talk about changes. I also added a column for total sales. What I would recommend, if you wanted to only see 2009 through 2012 is to delete lines 19 through 22 and add the following code between lines 46 and 47 (this would limit the total sales column to those years):

      AND year(SH.DOCDATE) between 2009 and 2012
      

      To add on hand quantity, you can add the following after line 18:

      Q.QTYONHND Quantity_on_Hand,  
      

      Add the following before the GROUP BY (line 49):

      LEFT OUTER JOIN IV00102 Q
      ON Q.ITEMNMBR = D.ITEMNMBR AND Q.RCRDTYPE = 1
      

      And the following at the end of the GROUP BY line (line 49):

      , Q.QTYONHND
      

      Hope that helps,
      -Victoria

      Like

  4. 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.

    Like

  5. 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!!!

    Like

    • 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:

      • What about anything not entered in the SOP module? (For example anything entered directly in the RM module.)
      • What amount should be used? (Subtotal, Invoice Amount, something else?)

      -Victoria

      Like

      • 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.

        Like

  6. 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

    Like

  7. Hi Victoria, i need this but instead of year by month, what do i need to change?

    Thanks,

    George

    Like

  8. 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

    Like

    • 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 be SD.QUANTITY instead.

      -Victoria

      Like

  9. 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’.

    Like

Trackbacks/Pingbacks

  1. SQL view for sales quantities by item by site by year – 2/28, Victoria Yudin | Partner Compete - February 28, 2013

    […] is another variation on my view for sales by item by year and view for sales quantities by item by year. This one shows the total item quantity sold by […]

    Like

  2. SQL view for sales quantities by item by site by year | Victoria Yudin - February 28, 2013

    […] is another variation on my view for sales by item by year and view for sales quantities by item by year. This one shows the total item quantity sold by […]

    Like

  3. SQL view for sales quantities by item by year | Victoria Yudin - January 25, 2012

    […] 23, 2012 by Victoria Yudin 1 Comment Even since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There […]

    Like

  4. Interesting Findings & Knowledge Sharing » SQL view for sales quantities by item by year - January 23, 2012

    […] since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There […]

    Like

  5. SQL view for sales quantities by item by year - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 23, 2012

    […] 0 Even since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There […]

    Like

  6. SQL view for sales quantities by item by year | Victoria Yudin - January 23, 2012

    […] 23, 2012 by Victoria Yudin 0 Comments Even since I published my view for sales by item by year I started receiving requests for the same type of view showing quantities instead of amounts. There […]

    Like

  7. SQL view for sales by item by month - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - August 29, 2011

    […] 0 Here is a new Dynamics GP SQL view in reponsse to a reader that liked my view for sales by item by year, but wanted to see the same thing by month. I am making a few assumptions (listed in the view […]

    Like

  8. SQL view for sales by item by month | Victoria Yudin - August 29, 2011

    […] 0 Comments Here is a new Dynamics GP SQL view in reponsse to a reader that liked my view for sales by item by year, but wanted to see the same thing by month. I am making a few assumptions (listed in the view […]

    Like

  9. SQL view for sales by item by year | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - August 15, 2011

    […] Comments 0 Victoria Yudin adds a new SQL view for sales by item by year […]

    Like

  10. Everything Dynamics GP #19 - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community - August 12, 2011

    […] SQL view for sales by item by year (Victoria Yudin) has posted this useful SQL View.  […]

    Like

  11. SQL view for sales by item by year | Interesting Findings & Knowledge Sharing - August 11, 2011

    […] from: SQL view for sales by item by year VN:F [1.9.10_1130]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: