The #Inventory table has duplicate item numbers with different balances and standard costs by trans date. How can I insert/select the most recent record from #Inventory using trans date?
Code:
Insert Into #Balance
( ItemNumber
, TransDate
, Balance
, StdCost
, StdCostValue )
Select '(' + RTrim(i.ItemNumber) + ') ' + RTrim(s.ItemDescription)
, TransDate
, Balance
, StdCost
, StdCost * Balance
From #Inventory i Inner Join #SelectedItems s On i.ItemNumber = s.ItemNumber
Where '(' + RTrim(i.ItemNumber) + ') ' + RTrim(s.ItemDescription) Not In (Select ItemNumber From #Balance)
Group By '(' + RTrim(i.ItemNumber) + ') ' + RTrim(s.ItemDescription)