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:

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 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 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.

16 Responses to “SQL view for sales quantities by item by site by month in Dynamics GP”

  1. How can I add Qty, so the view will show Qty and Sales for the 12-Months?

    Like

    • HI Chuck,

      Sorry, I am not sure what you mean…this already shows the sales quantities. Can you please give a little more detail as to what you’re looking for?

      -Victoria

      Like

      • Sales Quantities & Dollars. I have learned a ton of SQL reading your blog, and have even been able to use many of yor scripts, and modify them for my purposes, but this one was beyond me. Essentially, I want to be able to have a script that shows Item, Sales Quantity and Sales Dollars by month.

        Like

        • Hi Chuck,

          I just posted this new view combining the quantities and amounts per month – hopefully that’s what you were looking for.

          -Victoria

          Like

          • Victoria,

            Thank you, that is awesome & I see my error!
            Your blog is my go-to place for GP & SQL.

            Chuck

            Like

            • Chuck,

              Glad to help!

              -Victoria

              Like

              • Victoria,

                Is there a way to use variables in creating column names? For example, I’d like to do something like this:

                sum(case when month(d.DOCDATE) = 1 and year(d.DOCDATE) = year(getdate())
                then d.Qty else 0 end) as JAN + YEAR(GETDATE),

                This way, I can run the view to look at multiple years & have them labeled correctly.

                Like

                • Chuck, I am not aware of any easy way to do this directly in SQL. I believe this blog talks about options for possibly accomplishing this with PIVOT and dynamic names: http://www.kodyaz.com/articles/t-sql-dynamic-pivot-table-example-code.aspx.

                  One way that I have done this in the past is by adding formulas to column names in Crystal Reports or SSRS. However, you might be better off with the SQL code created a different way (with the data in rows instead of columns) to be be able to more easily use a reporting tool for that. Or better yet, with a reporting tool, you can just use a parameter to specify the year.

                  -Victoria

                  Like

                  • Victoria,
                    Thanks, I’ve looked around other places & tried several options (concat, etc) and nothing worked. I’ll just go with CY and PY, that should satisfy my users. Thanks again for your help.

                    Chuck

                    Like

  2. Like many developers, I’m sure you cringe when you have to include hard code (in this case, year). Perhaps you could show how to the same in a table function with year passed as a parameter.

    Ken

    Like

  3. This is perfect for what I was looking to analyze. Now we can do it for any year too. Thank you so much.

    Like

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP sales quantities and amounts by item by site by month – 10/15, Victoria Yudin | - October 15, 2013

    […] by request for one of my blog readers. This view combines my Sales by Item by Month and Sales Quantities by Item by Site by Month views. It will return columns for each month with the quantity and sales amount side by […]

    Like

  2. SQL view for Dynamics GP sales quantities and amounts by item by site by month - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - October 15, 2013

    […] created by request for one of my blog readers. This view combines my Sales by Item by Month and Sales Quantities by Item by Site by Month views. It will return columns for each month with the quantity and sales amount side by side. The […]

    Like

  3. SQL view for Dynamics GP sales quantities and amounts by item by site by month | Victoria Yudin - October 15, 2013

    […] created by request for one of my blog readers. This view combines my Sales by Item by Month and Sales Quantities by Item by Site by Month views. It will return columns for each month with the quantity and sales amount side by side. The […]

    Like

  4. SQL view for sales quantities by item by site by month in Dynamics GP | Victoria Yudin | DynamicAccounting.net - June 17, 2013

    […] Victoria provides a SQL view for sales quantities by item by site by month in Dynamics GP […]

    Like

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,566 other followers

%d bloggers like this: