forklift

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:

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 http://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.

Trackbacks/Pingbacks

  1. SQL view for sales quantities by item by site by year | Victoria Yudin - DynamicAccounting.net - Microsoft Dynamics GP - Microsoft Dynamics Community - March 4, 2013

    [...] Victoria Yudin gives us a SQL view for sales quantities by item by site by year [...]

    Like this

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

    [...] Continue reading on Source Blog [...]

    Like this

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

Follow

Get every new post delivered to your Inbox.

Join 1,357 other followers

%d bloggers like this: