Microsoft Dynamics Great Plains - Annual Historical Usage query

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.

SELECT SD.ITEMNMBR,

        SUM(CASE SD.SOPTYPE WHEN 3 THEN SD.QTYFULFI*QTYBSUOM

                            WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1

            END) Annual_Historical_Usage

 FROM SOP30200 SH

INNER JOIN

     SOP30300 SD

     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.