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?
