SQL view for sales quantities by customer by item by year

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.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s