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.

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

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

  2. 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,532 other followers

%d bloggers like this: