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:

• Sales Order Processing (SOP) tables
• Inventory tables
• Sales Order Processing (SOP) SQL views
• Inventory SQL views
• GP Reports (there is a section for General Ledger reports under Dynamics GP SQL Scripts)

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, Inc.
-- 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%'   -- excludes alignment forms
     ) 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.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code, SOP SQL code Tagged: featured, GP Reports code, Inventory, Sales Order Processing, SQL code

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

Tags: dynamics, Dynamics GP, events, forums, General Ledger, gp reports code, great plains, inventory sql code, Sales Order Processing, Smartlist Builder, sql server, vaidy-mohan

Category: Blogs I Follow