Insert record having most recent trans date

[cs2009]

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?

		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)

 

[markros]

Are you sure your where clause is correct? Do you keep (ItemNumber) Description as ItemNumber in the Balance table? Is the ItemNumber unique in the Inventory?

Anyway, try:

;with cte as (Select '(' + RTrim(i.ItemNumber) + ') ' + RTrim(s.ItemDescription) as FullItemName, 
                       row_number() over (partition by i.ItemNumber, s.ItemDescription order by i.TransDate DESC) as Row
			 , i.TransDate 
			 , i.Balance
			 , i.StdCost
			 , i.StdCost * i.Balance as TotalAmount
		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))

select * from cte where Row = 1 -- latest transaction date

PluralSight Learning Library