>>I don't know what I did, but this query I have no longer works. I know I've posted on this multiple times, but for some reason
>>it no longer returns the right results.
>>
>>Again, I have a table of inventory. Here is a sample of the data:
>>
>>

>>   InventoryId PartId WarehouseId LocationId SerialNo ActionDate
>>1  17147       17097  22	  293	     NULL     2009-06-19 10:20:58.977
>>2  19283       17097  22	  262	     NULL     2009-06-19 10:03:43.033
>>3  17148       17098  22	  262	     NULL     2009-06-19 10:03:43.033
>>4  17151       17101  22	  262	     NULL     2009-06-19 10:03:43.033
>>5  17152       17102  22	  262	     NULL     2009-06-19 10:03:43.033
>>6  17153       17103  22	  262	     NULL     2009-06-19 10:03:43.033
>>7  19285       17104  23        268      ABC123   2009-06-19 11:04:40.687 	
>>8  17154       17104  23	  0	     ABC123   2009-06-19 10:55:22.437
>>9  19284       17104  22	  262	     NULL     2009-06-19 10:03:43.033
>>10 17155       17105  22	  262	     NULL     2009-06-19 10:03:43.033
>>

>>
>>The first two rows show that part 17097 was moved in the same warehouse from location 262 to 293. Rows 7
>>through 9 show that part 17104 was moved from location 262 at warehouse 22 to warehouse 23, and was then
>>put on location 268.
>>
>>So, this query need only to pull rows 1,3,4,5,6 and 10. The remaining rows are either no longer at warehouse 22 or
>>are previous location records that no longer apply.
>>
>>Here's what I have. It returns any row with a 22 in WarehouseId.
>>
>>

>>DECLARE @WarehouseId INT
>>SET @WarehouseId = 22
>>
>>SELECT i.*, i.LocationId, i.LocationInfo, l.Location, p.PartNo, p.Description, i.SerialNo
>>	FROM Inventory i
>>	JOIN Locations l ON l.LocationId = i.LocationId
>>	JOIN Parts p ON p.PartId = i.PartId
>>	WHERE i.WarehouseId = @WarehouseId AND
>>		  i.ActionDate =
>>			(SELECT MAX(i2.ActionDate)
>>				FROM Inventory i2
>>				WHERE i2.InventoryId = i.InventoryId)
>>	ORDER BY PartId, ActionDate DESC
>>

>>
>>I'm open to a better design if it would make working with this data easier.
>
>Kevin,
>
>Didn't we discuss this 10 times or more? Do you want SQL Server 2005 and up syntax or SQL Server 2000 compatible syntax?
>
>To fix your query (and it's SQL Server 2000 compatible version)
>

>>SELECT i.*, i.LocationId, i.LocationInfo, l.Location, p.PartNo, p.Description, i.SerialNo
>>	FROM Inventory i
>>	JOIN Locations l ON l.LocationId = i.LocationId
>>	JOIN Parts p ON p.PartId = i.PartId
>>	WHERE i.WarehouseId = @WarehouseId AND
>>		  i.ActionDate =
>>			(SELECT MAX(i2.ActionDate)
>>				FROM Inventory i2
>>				WHERE i2.PartID = i.PartID AND
>i2.WarehouseId = i.WarehouseId )
>>	ORDER BY PartId, ActionDate DESC
>>

>
>Let me know if you want version with ROW_NUMBER() which I prefer nowdays any other solution.



>Didn't we discuss this 10 times or more? Do you want SQL Server 2005 and up syntax or SQL Server 2000 compatible syntax?
It certainly seems that way. I have no idea what I did, but it wasn't working. I have been looking at it all morning and I didn't see
why it was broken.

At any rate, it's ok now. Again

Thanks!

.