3d boxes

SQL view for sales quantities by item by year


Ever 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 are two ways of doing this, once from inventory and another from sales.  Hard to say which is the best, as I have seen arguments for both, but I prefer to do this from the SOP module. The view below makes a number of assumptions (listed in the view comments in green), and I am hard coding years from 2000 through 2013 as well as adding an overall total column at the end. You can easily change the years or add new ones by following the example in my code.

To see additional Dynamics GP SOP code please take look at this page. For additional GP reporting information and links, check out my GP Reports page.


create view view_Sales_Qty_by_Item_by_Year
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--view_Sales_Qty_by_Item_by_Year
--Created Jan 23, 2012 by Victoria Yudin - Flexible Solutions Inc
--For updates see https://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (SOP invoices less 
--  returns) for each item by year 
--Only posted invoices and returns are included
--Quantity is calculated by multiplying by QTYBSUOM column in 
--  case other UofM's are used on transations
--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 Jun 14, 2013 to add year 2013
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT
D.ITEMNMBR Item_Number, D.Item_Description,
sum(case when year(D.DOCDATE) = 2000 
    then D.Qty else 0 end) as [2000_Qty],
sum(case when year(D.DOCDATE) = 2001 
    then D.Qty else 0 end) as [2001_Qty],
sum(case when year(D.DOCDATE) = 2002 
    then D.Qty else 0 end) as [2002_Qty],
sum(case when year(D.DOCDATE) = 2003 
    then D.Qty else 0 end) as [2003_Qty],
sum(case when year(D.DOCDATE) = 2004 
    then D.Qty else 0 end) as [2004_Qty],
sum(case when year(D.DOCDATE) = 2005 
    then D.Qty else 0 end) as [2005_Qty],
sum(case when year(D.DOCDATE) = 2006 
    then D.Qty else 0 end) as [2006_Qty],
sum(case when year(D.DOCDATE) = 2007 
    then D.Qty else 0 end) as [2007_Qty],
sum(case when year(D.DOCDATE) = 2008 
    then D.Qty else 0 end) as [2008_Qty],
sum(case when year(D.DOCDATE) = 2009 
    then D.Qty else 0 end) as [2009_Qty],
sum(case when year(D.DOCDATE) = 2010 
    then D.Qty else 0 end) as [2010_Qty],
sum(case when year(D.DOCDATE) = 2011 
    then D.Qty else 0 end) as [2011_Qty],
sum(case when year(D.DOCDATE) = 2012 
    then D.Qty else 0 end) as [2012_Qty],
sum(case when year(D.DOCDATE) = 2013 
    then D.Qty else 0 end) as [2013_Qty],
sum(D.Qty) Total_Qty

FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
 coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
 CASE SD.SOPTYPE
     WHEN 3 THEN SD.QTYFULFI*QTYBSUOM
     WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1
     END Qty
 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.ITEMNMBR not like 'XXXXXXXXXXXXXXX%') D

GROUP BY D.ITEMNMBR, D.Item_Description

GO
GRANT SELECT ON view_Sales_Qty_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.

24 Responses to “SQL view for sales quantities by item by year”

  1. Victoria

    I can run this view in SQL but Crystal keeps getting an error. It notes Incorrect syntax near ‘.2006’. Any ideas?

    Like

  2. Victoria, How can I modify this to see Quantity Sold by Item Number and broken out then by site, year, and then month. To try and account for seasonal shifts. Thanks in advance.

    Like

  3. Could this be done by site?

    Like

Trackbacks/Pingbacks

  1. SQL view for sales quantities by item by site by month in Dynamics GP : Interesting Findings & Knowledge Sharing - June 14, 2013

    […] there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for a hard-coded year. I am […]

    Like

  2. SQL view for sales quantities by item by site by month in Dynamics GP - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - June 14, 2013

    […] there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for a hard-coded year. I am […]

    Like

  3. SQL view for sales quantities by item by site by month in Dynamics GP – 6/14, Victoria Yudin | Partner Compete - June 14, 2013

    […] there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for a hard-coded year. I […]

    Like

  4. SQL view for sales quantities by item by site by month in Dynamics GP | Victoria Yudin - June 14, 2013

    […] there is always another one to be had. A request from a reader brings us a monthly version of my Sales Quantities by Item by Year. The view below shows the total item quantity sold by site by month for a hard-coded year. I am […]

    Like

  5. 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 site by year. The view below makes a number of […]

    Like

  6. 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 site by year. The view below makes a number of […]

    Like

  7. Interesting Findings & Knowledge Sharing » SQL view for sales quantities by customer by item by year - March 9, 2012

    […] there are, people will always want more. I recently had a request for something similar to my view for sales quantities by item by year, but also adding in the customer.  Below is code for a view to accomplish this. This view makes a […]

    Like

  8. SQL view for sales quantities by customer by item by year | Victoria Yudin - March 9, 2012

    […] there are, people will always want more. I recently had a request for something similar to my view for sales quantities by item by year, but also adding in the customer.  Below is code for a view to accomplish this. This view makes a […]

    Like

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

    […] more here: SQL view for sales quantities by item by year VN:F [1.9.13_1145]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

  10. SQL view for sales quantities by item by year | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - January 24, 2012

    […] Comments 0 Victoria Yudin offers up a SQL view for sales quantities by item by year […]

    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: