0 Replies Latest reply: Jun 20, 2013 6:42 AM by Marvin Mindanao RSS

    Inventory Report Problem

      I have a problem in getting the Beginning Inventory. Table Details.

      1. I have  a History Trail Table. It contains Item No., On-hand Quantity, Begin Date and End Date.

      2. There are multiple Records per Item No. (but different Begin Date)

       

      What I want to do:

      1. First is to filter the records by the Selected Date. All record that have Begin Date less than my Selected Date.

      2. Then get the Items On-hand Quantity, (only one record per Item No. and only the latest record per Item. I want to get the Item Record based on the rescent begin date of the Item.)

       

      Item_#     Begin_date     End_Date     OH_QTY     STORE_NO

      1               2013-01-05     2013-02-05     10               1

      1               2013-04-01     2013-05-01     5                 2

      1               2012-05-01     2012-07-06     10               1

      2               2013-06-01     2013-07-19     50                2

      2               2013-01-24     2013-03-10     13                1

      3               2011-06-05     2013-06-29     10               1

      3               2012-05-01     2013-02-17     7                 1

      3               2013-01-01     2013-10-12     60                2  

       

       

      I want to get all items with begin_date < '2013-05-27'. the TOTAL On-hand Quantity should be 30.. I need to get the Highlighted Records above. What is the set analysis to be used?