One of my clients recently asked me to create an Inventory report off of Microsoft Dynamics GP(Great Plains) . One of the fields he wanted to include in the report was Annual Historical Usage that shows up in the Item History window as shown below. I finished the report in a couple of hours but during testing, the Annual Historical Usage in my report was not tying with what shows up in GP in the Item History window. After running some ad-hoc queries, I was able to figure out what was going on and want to share the solution here on my blog.
Below is the query I used to pull Annual Historical Usage. You can run it for a specific item number or all item numbers. The trick here is to figure out how GP calculates this number – GP calculates it by summing up transactions starting from the first day of last year’s current month to the last day of current year's previous month. For example, if the current month is November 2012, then the range would be 2011-11-01 to 2012-10-31.
SUM(CASE SD.SOPTYPE WHEN 3 THEN SD.QTYFULFI*QTYBSUOM
WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1
FROM SOP30200 SH
ON SD.SOPNUMBE = SH.SOPNUMBE
AND SD.SOPTYPE = SH.SOPTYPE
WHERE SH.VOIDSTTS = 0
AND SH.SOPTYPE IN (3,4)
AND SH.DOCDATE BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, '19010101', GETDATE()), '19000101') AND DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231')
--AND SD.ITEMNMBR IN (@ItemNumber)
AND SH.LOCNCODE = 'AUS'
GROUP BY SD.ITEMNMBR
I hope this is helpful.