SQL view for sales quantities by item by site by year

Below 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 assumptions (listed in the view comments in green), and I am hard coding years from 2006 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.

Some additional resources:

• Sales Order Processing (SOP) SQL views
• Sales Order Processing (SOP) commonly used tables
• Other GP Reporting links

CREATE VIEW view_Sales_Qty_by_Item_Site_Year
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Sales_Qty_by_Item_Site_Year
-- Created Feb 28, 2013 by Victoria Yudin, Flexible Solutions Inc
-- For updates see https://victoriayudin.com/gp-reports/
-- Returns total sales quantities fulfilled (invoices less 
--    returns) for each item by site 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 transactions
-- Voided transactions 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, 
D.LOCNCODE Site_ID,
sum(case when year(D.DOCDATE) = 2006 
    then D.Qty else 0 end) [2006_Qty],
sum(case when year(D.DOCDATE) = 2007 
    then D.Qty else 0 end) [2007_Qty],
sum(case when year(D.DOCDATE) = 2008 
    then D.Qty else 0 end) [2008_Qty],
sum(case when year(D.DOCDATE) = 2009 
    then D.Qty else 0 end) [2009_Qty],
sum(case when year(D.DOCDATE) = 2010 
    then D.Qty else 0 end) [2010_Qty],
sum(case when year(D.DOCDATE) = 2011 
    then D.Qty else 0 end) [2011_Qty],
sum(case when year(D.DOCDATE) = 2012 
    then D.Qty else 0 end) [2012_Qty],
sum(case when year(D.DOCDATE) = 2013 
    then D.Qty else 0 end) [2013_Qty],
sum(D.Qty) Total_Qty

from 
(select SH.DOCDATE, SD.ITEMNMBR, SD.LOCNCODE,
 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, D.LOCNCODE

go
grant select on view_Sales_Qty_by_Item_Site_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.