SQL view for sales quantities by customer by item by year

March 9, 2012 by Victoria Yudin 12 Comments


Seems like no many how many variations of sales reports 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 number of assumptions (listed in the view comments in green), and I am hard coding years from 2003 through 2012 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.

You can find more code like this on my SOP SQL Views and Inventory SQL Views pages. For additional GP reporting information and links, check out my GP Reports page.


CREATE VIEW view_Sales_Qty_by_Customer_Item_Year
AS

--***********************************************************************************
--view_Sales_Qty_by_Customer_Item_Year
--Created Mar 9, 2012 by Victoria Yudin - Flexible Solutions, Inc.
--For updates see https://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (invoices - returns) for each item
--     by customer 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
--Invoice/Return dates are used, not GL posting dates 
--Item Description is taken from Inventory Item Maintenance for all inventory items
--     and from SOP line items for non-inventory items
--***********************************************************************************

SELECT
C.CUSTNMBR Customer_ID, C.CUSTNAME Customer_Name,
D.ITEMNMBR Item_Number, D.Item_Description,
sum(case when year(D.DOCDATE) = 2003 then D.Qty else 0 end) as [Qty_in_2003],
sum(case when year(D.DOCDATE) = 2004 then D.Qty else 0 end) as [Qty_in_2004],
sum(case when year(D.DOCDATE) = 2005 then D.Qty else 0 end) as [Qty_in_2005],
sum(case when year(D.DOCDATE) = 2006 then D.Qty else 0 end) as [Qty_in_2006],
sum(case when year(D.DOCDATE) = 2007 then D.Qty else 0 end) as [Qty_in_2007],
sum(case when year(D.DOCDATE) = 2008 then D.Qty else 0 end) as [Qty_in_2008],
sum(case when year(D.DOCDATE) = 2009 then D.Qty else 0 end) as [Qty_in_2009],
sum(case when year(D.DOCDATE) = 2010 then D.Qty else 0 end) as [Qty_in_2010],
sum(case when year(D.DOCDATE) = 2011 then D.Qty else 0 end) as [Qty_in_2011],
sum(case when year(D.DOCDATE) = 2012 then D.Qty else 0 end) as [Qty_in_2012],
sum(D.Qty) Total_Qty

FROM
(SELECT SH.DOCDATE, SH.CUSTNMBR, 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

LEFT OUTER JOIN RM00101 C
  ON C.CUSTNMBR = D.CUSTNMBR

GROUP BY D.ITEMNMBR, D.Item_Description, C.CUSTNMBR, C.CUSTNAME

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

Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code
, , , ,

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

  1. Judy Olivarez Reply December 5, 2018 at 10:46 am

    Love the sales by customer by item but would like sales by customer by $ year over year

    Like

  2. Brenda Reply May 23, 2012 at 1:15 pm

    Thank you for your help Victoria!

    Like

  3. Brenda Reply May 22, 2012 at 5:36 pm

    Victoria: I appreciate the views you make available to us. I worked on this to put Quantity by Location Code instead of customer, and then month instead of year. However, I don’t believe it is pulling correctly. (I tried to pull information from various views you created.) This is the code I used.

    SELECT
    D.ITEMNMBR Item_Number, D.Item_Description, D.LOCNCODE,
    sum(case when month(D.DOCDATE) = 1 then D.Qty else 0 end) as [Qty_in_Jan],
    sum(case when month(D.DOCDATE) = 2 then D.Qty else 0 end) as [Qty_in_Feb],
    sum(case when month(D.DOCDATE) = 3 then D.Qty else 0 end) as [Qty_in_Mar],
    sum(case when month(D.DOCDATE) = 4 then D.Qty else 0 end) as [Qty_in_Apr],
    sum(case when month(D.DOCDATE) = 5 then D.Qty else 0 end) as [Qty_in_May],
    sum(case when month(D.DOCDATE) = 6 then D.Qty else 0 end) as [Qty_in_June],
    sum(case when month(D.DOCDATE) = 7 then D.Qty else 0 end) as [Qty_in_July],
    sum(case when month(D.DOCDATE) = 8 then D.Qty else 0 end) as [Qty_in_Aug],
    sum(case when month(D.DOCDATE) = 9 then D.Qty else 0 end) as [Qty_in_Sept],
    sum(case when month(D.DOCDATE) = 10 then D.Qty else 0 end) as [Qty_in_Oct],
    sum(case when month(D.DOCDATE) = 11 then D.Qty else 0 end) as [Qty_in_Nov],
    sum(case when month(D.DOCDATE) = 12 then D.Qty else 0 end) as [Qty_in_Dec],
    sum(D.Qty) Total_Qty

    FROM
    (SELECT SH.DOCDATE, SH.CUSTNMBR, SH.LOCNCODE, 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
    AND SD.LOCNCODE = SH.LOCNCODE
    LEFT OUTER JOIN
    IV00101 I
    ON I.ITEMNMBR = SD.ITEMNMBR
    AND I.LOCNCODE = SD.LOCNCODE
    WHERE SH.VOIDSTTS = 0
    AND SH.SOPTYPE IN (3,4)
    AND SH.LOCNCODE not like ‘XXXXXXXXXXXXXXX%’) D

    GROUP BY D.ITEMNMBR, D.Item_Description, d.LOCNCODE

    Like

    • Victoria Yudin Reply May 23, 2012 at 2:59 am

      Hi Brenda,

      I don’t think this will work properly, a couple of questions first to make sure I understand what you’re looking to do:

      1. Right now the code is grouping all sales per month regardless of year. In other words, if you have sales data starting in 2010, the March column would show March 2010 + March 2011 + March 2012. Is that what you want?
      2. Do you want the Site ID from the line items or the transaction header?

      -Victoria

      Like

      • Brenda Reply May 23, 2012 at 9:41 am

        Victoria: I want the sales to group by each month of one year … Jan, Feb, Mar…Dec 2012. The Site ID can come from either. We complete sales by site only — we do not mix the sales per site. Thank you for your help!

        Like

        • Victoria Yudin Reply May 23, 2012 at 12:52 pm

          Brenda,

          If you want to hardcode this for 2012, try the following code:

          SELECT
D.ITEMNMBR Item_Number, D.Item_Description, D.LOCNCODE Site_ID,
sum(case when month(D.DOCDATE) = 1 then D.Qty else 0 end) as [Qty_in_Jan],
sum(case when month(D.DOCDATE) = 2 then D.Qty else 0 end) as [Qty_in_Feb],
sum(case when month(D.DOCDATE) = 3 then D.Qty else 0 end) as [Qty_in_Mar],
sum(case when month(D.DOCDATE) = 4 then D.Qty else 0 end) as [Qty_in_Apr],
sum(case when month(D.DOCDATE) = 5 then D.Qty else 0 end) as [Qty_in_May],
sum(case when month(D.DOCDATE) = 6 then D.Qty else 0 end) as [Qty_in_June],
sum(case when month(D.DOCDATE) = 7 then D.Qty else 0 end) as [Qty_in_July],
sum(case when month(D.DOCDATE) = 8 then D.Qty else 0 end) as [Qty_in_Aug],
sum(case when month(D.DOCDATE) = 9 then D.Qty else 0 end) as [Qty_in_Sept],
sum(case when month(D.DOCDATE) = 10 then D.Qty else 0 end) as [Qty_in_Oct],
sum(case when month(D.DOCDATE) = 11 then D.Qty else 0 end) as [Qty_in_Nov],
sum(case when month(D.DOCDATE) = 12 then D.Qty else 0 end) as [Qty_in_Dec],
sum(D.Qty) Total_Qty
FROM
(SELECT SH.DOCDATE, SH.LOCNCODE, 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%'
AND year(SH.DOCDATE) = 2012) D
GROUP BY D.ITEMNMBR, D.Item_Description, d.LOCNCODE

          -Victoria

          Like

          • Justin J. Marshall Reply January 28, 2014 at 6:53 pm

            Hello Victoria,

            Thanks a bunch for this great code. I am running the SQL view above which is broken down by month and it is working great. Only thing is that the quantity columns are formatted as currency in SmartList. Can you tell me how to remove the currency format for the quantities?

            Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL view for sales quantities by customer by item by year | Victoria YudinSQL view for sales quantities by customer by item by year | Victoria Yudin - DynamicAccounting.net - May 9, 2016

    […] Victoria Yudin has a new SQL view for sales quantities by customer by item by year […]

    Like
  2. SQL view for sales quantities by customer by item by year | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - March 12, 2012

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

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

    […] this link: SQL view for sales quantities by customer by item by year VN:F [1.9.15_1155]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

Leave a comment