Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert record having most recent trans date

Status
Not open for further replies.

cs2009

Programmer
Aug 10, 2009
50
US
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)
 
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:

Code:
;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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top