3d small people - diagram

SQL view for rolling 12 months of sales by item in Dynamics GP


Rolling twelve month reports are not always very easy to create,  and I have been seeing more requests for them, so I thought I would show an example of one way to do this. The view below is a variation of my sales by item by month view, however instead of specifying all months in a year, this will return the last 12 months, not including the current month. Note that this will be using the current date on the SQL server, not the user date specified in GP. As usual, I am making some assumptions which are listed in the view comments.

Related code and table information:

create view view_Rolling_12_Mo_Sales_by_Item
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Rolling_12_Mo_Sales_by_Item
-- Created Aug 27, 2012 by Victoria Yudin, Flexible Solutions
-- For updates see http://victoriayudin.com/gp-reports/
-- Returns total sales (invoices - returns) for each item for 
--   the last 12 months. Current month is not included, 
--   even if it is the last day of the month.
-- Only posted invoices and returns are included.
-- 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.
-- Document Date is used (not GL Posting Date).
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 

SELECT
D.ITEMNMBR Item_Number,
D.Item_Description,
D.Generic_Description,
D.Item_Class,
D.User_Category_1,
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -12, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -12, GETDATE()))
  then D.SALES else 0 end) as [Sales_12_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -11, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -11, GETDATE()))
  then D.SALES else 0 end) as [Sales_11_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -10, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -10, GETDATE()))
  then D.SALES else 0 end) as [Sales_10_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -9, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -9, GETDATE()))
  then D.SALES else 0 end) as [Sales_9_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -8, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -8, GETDATE()))
  then D.SALES else 0 end) as [Sales_8_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -7, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -7, GETDATE()))
  then D.SALES else 0 end) as [Sales_7_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -6, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -6, GETDATE()))
  then D.SALES else 0 end) as [Sales_6_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -5, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -5, GETDATE()))
  then D.SALES else 0 end) as [Sales_5_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -4, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -4, GETDATE()))
  then D.SALES else 0 end) as [Sales_4_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -3, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -3, GETDATE()))
  then D.SALES else 0 end) as [Sales_3_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -2, GETDATE()))
  and year(D.DOCDATE) = year(DATEADD(m, -2, GETDATE()))
  then D.SALES else 0 end) as [Sales_2_mo_ago],
sum(case when month(D.DOCDATE) = 
  month(DATEADD(m, -1, GETDATE()))
  and YEAR(D.DOCDATE) = year(DATEADD(m, -1, GETDATE()))
  then D.SALES else 0 end) as [Sales_1_mo_ago]

FROM
(SELECT SH.DOCDATE, SD.ITEMNMBR,
 coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
 coalesce(I.ITMGEDSC, '') Generic_Description,
 coalesce(I.ITMCLSCD,'') Item_Class,
 coalesce(I.USCATVLS_1,'') User_Category_1,
 case SD.SOPTYPE
     WHEN 3 THEN SD.XTNDPRCE
     WHEN 4 THEN SD.XTNDPRCE*-1
     END SALES
 FROM SOP30200 SH  -- SOP header
 INNER JOIN
     SOP30300 SD  -- SOP lines
     ON SD.SOPNUMBE = SH.SOPNUMBE
     AND SD.SOPTYPE = SH.SOPTYPE
 LEFT OUTER JOIN
     IV00101 I  -- item master
     ON I.ITEMNMBR = SD.ITEMNMBR
 WHERE SH.VOIDSTTS = 0   -- not voided
     AND SH.SOPTYPE IN (3,4) -- only invoices and returns
     AND SD.XTNDPRCE <> 0   -- excludes zero price
     AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%'  
     ) D

GROUP BY D.ITEMNMBR, D.Item_Description, 
  D.Generic_Description, D.Item_Class, D.User_Category_1

-- add permissions for DYNGRP
GO
GRANT SELECT ON view_Rolling_12_Mo_Sales_by_Item 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.

10 Responses to “SQL view for rolling 12 months of sales by item in Dynamics GP”

  1. Hi Victoria,
    I hope you are well.

    I am once again plagued by our various unit of measures and am trying to make a report that summarizes the item sales by item quantity and unit of measure (from SOP) for the past year.

    I can’t seem to get the sale quantity summarized for the past year and appreciate any insight you can provide. I probably should have started with your Sales Quantities by Items by Year view however I was trying to make a solution that wouldn’t require someone with access to SQL to modify it for additional years as necessary.

    SELECT
    D.ITEMNMBR Item_Number,
    D.Item_Description,
    D.Generic_Description,
    D.Item_Class,
    D.UOFM,
    sum(case when year(D.DOCDATE) = year(DATEADD(y, -1, GETDATE()))
    then D.SALES else 0 end) as [SalesLast12Months] –I was thinking this would get the sales in the past year summarized

    FROM
    (SELECT SH.DOCDATE, SD.ITEMNMBR,
    coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
    coalesce(I.ITMGEDSC, ”) Generic_Description,
    coalesce(I.ITMCLSCD,”) Item_Class,
    coalesce(SD.UOFM,”) UOFM, –Changed from UserCategory
    case SD.SOPTYPE
    WHEN 3 THEN SD.QUANTITY –Changed from extended price
    WHEN 4 THEN SD.QUANTITY*-1
    END SALES
    FROM SOP30200 SH — SOP header
    INNER JOIN
    SOP30300 SD — SOP lines
    ON SD.SOPNUMBE = SH.SOPNUMBE
    AND SD.SOPTYPE = SH.SOPTYPE
    LEFT OUTER JOIN
    IV00101 I — item master
    ON I.ITEMNMBR = SD.ITEMNMBR
    WHERE SH.VOIDSTTS = 0 — not voided
    AND SH.SOPTYPE IN (3,4) — only invoices and returns
    AND SD.ITEMNMBR = ‘P214′ — just a good item for me to test against remove before creating view
    AND SD.ITEMNMBR not like ‘XXXXXXXXXXXXXXX%’
    ) D

    GROUP BY D.ITEMNMBR, D.Item_Description,
    D.Generic_Description, D.Item_Class, D.UOFM

    Thank you,
    Kirk

    Like

    • Hi Kirk,

      I would recommend putting the date selection into the where clause in D, that way, instead of getting ALL the data first, then summarizing based on the dates you want, you’re only getting the data you need, then summarizing it. If you don’t have a huge amount of data, it should not be a big difference, but once you have a large dataset you’re reporting against, you will get much better performance this way.

      I wasn’t sure if you want the sales for the past year or a rolling 12 months of sales? Your formula seems to suggest the former, but your description suggests the latter.

      If you’re looking to get all sales for last year (ie, it’s now 2015, show all of 2014), try this:

      SELECT
      D.ITEMNMBR Item_Number,
      D.Item_Description,
      D.Generic_Description,
      D.Item_Class,
      D.UOFM,
      sum(D.SALES) as [SalesLastYear] --I was thinking this would get the sales in the past year summarized
      FROM
      (SELECT SH.DOCDATE, SD.ITEMNMBR,
      coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
      coalesce(I.ITMGEDSC, '') Generic_Description,
      coalesce(I.ITMCLSCD,'') Item_Class,
      coalesce(SD.UOFM,'') UOFM, --Changed from UserCategory
      case SD.SOPTYPE
      WHEN 3 THEN SD.QUANTITY --Changed from extended price
      WHEN 4 THEN SD.QUANTITY*-1
      END SALES
      FROM SOP30200 SH -- SOP header
      INNER JOIN
      SOP30300 SD -- SOP lines
      ON SD.SOPNUMBE = SH.SOPNUMBE
      AND SD.SOPTYPE = SH.SOPTYPE
      LEFT OUTER JOIN
      IV00101 I -- item master
      ON I.ITEMNMBR = SD.ITEMNMBR
      WHERE SH.VOIDSTTS = 0 -- not voided
      AND SH.SOPTYPE IN (3,4) -- only invoices and returns
      AND SD.ITEMNMBR = 'P214' -- just a good item for me to test against remove before creating view
      AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%'
      and year(SH.DOCDATE) = year(getdate() -1) --last year
      ) D
      GROUP BY D.ITEMNMBR, D.Item_Description,
      D.Generic_Description, D.Item_Class, D.UOFM

      If you want the last 12 months (so if today is 2/25/2015, you’d get all sales AFTER 2/25/2014), change the line with the comment ‘–last year’ to the following:

      and SH.DOCDATE >= DATEADD(yy, -1, GETDATE()) --rolling 12 months

      Hope that helps,
      -Victoria

      Like

      • Sorry, my first attempt did not format the comments in SQL correctly…I just updated it, so it will be correct on the blog if you want to copy from there.
        -Victoria

        Like

        • Thank you so much Victoria. This will work great for us.

          In looking at your original view I am puzzled why this piece is necessary.

          AND SD.ITEMNMBR not like ‘XXXXXXXXXXXXXXX%’

          I tested it without this section and didn’t have any unexpected results. Care to enlighten me?

          Thanks again,
          Kirk

          Like

          • Hi Kirk,

            Good catch! Sometimes when alignment forms are printed in GP and not correctly cleared out, you get transactions in the system with X’s for all the fields. It does not happen often, but I have one customer that I’ve done a lot of reporting for that has these, so I got used to putting that clause in my code. I don’t think it would hurt anything for most companies to take that out. :-)

            -Victoria

            Like

  2. Greetings… Love the query – thanks! I tweaked it by moving most of the date logic to the inner query. It does require SQL 2008 or above because of the CAST(ABC AS DATE) but that can be replaced with CAST(DATE, FLOOR(CAST(FLOAT, ABC))) for SQL 2005 and prior.

    You end up with a much smaller query… I just hope the formatting stays in tack :)

    Enjoy!

    John

    CREATE VIEW view_Rolling_12_Mo_Sales_by_Item
    AS
    SELECT
    Item_Number,
    Item_Description,
    Generic_Description,
    Item_Class,
    User_Category_1,
    SUM(CASE WHEN Months = 11 THEN SALES ELSE 0 END) AS Sales_12_mo_ago,
    SUM(CASE WHEN Months = 10 THEN SALES ELSE 0 END) AS Sales_11_mo_ago,
    SUM(CASE WHEN Months = 9 THEN SALES ELSE 0 END) AS Sales_10_mo_ago,
    SUM(CASE WHEN Months = 8 THEN SALES ELSE 0 END) AS Sales_9_mo_ago,
    SUM(CASE WHEN Months = 7 THEN SALES ELSE 0 END) AS Sales_8_mo_ago,
    SUM(CASE WHEN Months = 6 THEN SALES ELSE 0 END) AS Sales_7_mo_ago,
    SUM(CASE WHEN Months = 5 THEN SALES ELSE 0 END) AS Sales_6_mo_ago,
    SUM(CASE WHEN Months = 4 THEN SALES ELSE 0 END) AS Sales_5_mo_ago,
    SUM(CASE WHEN Months = 3 THEN SALES ELSE 0 END) AS Sales_4_mo_ago,
    SUM(CASE WHEN Months = 2 THEN SALES ELSE 0 END) AS Sales_3_mo_ago,
    SUM(CASE WHEN Months = 1 THEN SALES ELSE 0 END) AS Sales_2_mo_ago,
    SUM(CASE WHEN Months = 0 THEN SALES ELSE 0 END) AS Sales_1_mo_ago
    FROM
    (
    SELECT DATEDIFF(“mm”, SOP30200.DOCDATE, CAST(DATEADD(“dd”, -DATEPART(“dd”,GETDATE()),GETDATE()) AS DATE)) AS Months,
    SOP30300.ITEMNMBR AS Item_Number,
    ISNULL(IV00101.ITEMDESC, SOP30300.ITEMDESC) Item_Description,
    ISNULL(IV00101.ITMGEDSC, ”) Generic_Description,
    ISNULL(IV00101.ITMCLSCD,”) Item_Class,
    ISNULL(IV00101.USCATVLS_1,”) User_Category_1,
    CASE SOP30300.SOPTYPE
    WHEN 3 THEN SOP30300.XTNDPRCE
    WHEN 4 THEN SOP30300.XTNDPRCE*-1
    END SALES
    FROM SOP30200
    JOIN SOP30300 ON SOP30300.SOPNUMBE = SOP30200.SOPNUMBE
    AND SOP30300.SOPTYPE = SOP30200.SOPTYPE
    LEFT JOIN IV00101 ON IV00101.ITEMNMBR = SOP30300.ITEMNMBR
    WHERE SOP30200.VOIDSTTS = 0 — not voided
    AND SOP30200.SOPTYPE IN (3,4) — only invoices and returns
    AND SOP30300.XTNDPRCE 0 — excludes zero price
    AND SOP30300.ITEMNMBR NOT LIKE ‘XXXXXXXXXXXXXXX%’
    — Only look at orders from the first day of this month minus one year, through the last day of the previous
    AND SOP30200.DOCDATE BETWEEN CAST(DATEADD(“yy”, -1, DATEADD(“dd”, 1-DATEPART(“dd”,GETDATE()),GETDATE())) AS DATE)
    AND CAST(DATEADD(“dd”, -DATEPART(“dd”,GETDATE()),GETDATE()) AS DATE)
    ) LastYearOfSalesData
    GROUP BY
    Item_Number,
    Item_Description,
    Generic_Description,
    Item_Class,
    User_Category_1
    GO
    GRANT SELECT ON view_Rolling_12_Mo_Sales_by_Item TO DYNGRP

    Like

  3. It is helpful,Thanks.

    Like

Trackbacks/Pingbacks

  1. SQL view for rolling 12 months of sales by item in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - August 28, 2012

    […] Comments 0 Victoria Yuding has a new SQL view for rolling 12 months of sales by item in Dynamics GP […]

    Like

  2. SQL view for rolling 12 months of sales by item in Dynamics GP – 8/27, Victoria Yudin | Partner Compete - August 27, 2012

    […] Continue reading on Source Blog […]

    Like

  3. SQL view for rolling 12 months of sales by item in Dynamics GP : Interesting Findings & Knowledge Sharing - August 27, 2012

    […] article: SQL view for rolling 12 months of sales by item in Dynamics GP VN:F [1.9.20_1166]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

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

%d bloggers like this: