SQL view for sales quantities by item by site by month in Dynamics GP

As many variations for item quantity summaries as I think I have posted on this blog, 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 making a number of assumptions (listed in the view comments in green), and there is also an overall yearly total column at the end. You can easily change the year as needed on line 64.

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_Month
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
--view_Sales_Qty_by_Item_Site_Month
--Created Jun 14, 2013 by Victoria Yudin - Flexible Solutions Inc
--For updates see http://victoriayudin.com/gp-reports/
--Returns total sales quantities fulfilled (SOP invoices less
--  returns) for each item by month for hardcoded year
--Calendar months and Document Dates are used
--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
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

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 Jan_Qty,
sum(case when month(d.DOCDATE) = 2
    then d.Qty else 0 end) as Feb_Qty,
sum(case when month(d.DOCDATE) = 3
    then d.Qty else 0 end) as Mar_Qty,
sum(case when month(d.DOCDATE) = 4
    then d.Qty else 0 end) as Apr_Qty,
sum(case when month(d.DOCDATE) = 5
    then d.Qty else 0 end) as May_Qty,
sum(case when month(d.DOCDATE) = 6
    then d.Qty else 0 end) as Jun_Qty,
sum(case when month(d.DOCDATE) = 7
    then d.Qty else 0 end) as Jul_Qty,
sum(case when month(d.DOCDATE) = 8
    then d.Qty else 0 end) as Aug_Qty,
sum(case when month(d.DOCDATE) = 9
    then d.Qty else 0 end) as Sep_Qty,
sum(case when month(d.DOCDATE) = 10
    then d.Qty else 0 end) as Oct_Qty,
sum(case when month(d.DOCDATE) = 11
    then d.Qty else 0 end) as Nov_Qty,
sum(case when month(d.DOCDATE) = 12
    then d.Qty else 0 end) as Dec_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 year(sh.DOCDATE) = 2013 --change year as needed
     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_Month 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.