>>>>I have been working on an inventory tracking application, and I have posted messages here before about the queries I'm using. Now the customer's requirements have changed, so I need to rethink how I'm doing this.
>>>>
>>>>This is a subset of the inventory table showing relevent columns.
>>>>
>>>>

>>>>InventoryId PartId      WarehouseId LocationId  UserId      SerialNo       ActionDate              
>>>>----------- ----------- ----------- ----------- ----------- -------------- ----------------------- 
>>>>7           9307        15          NULL        36                         2009-07-27 13:48:17.767 
>>>>6           9307        15          199         36                    	   2009-07-27 14:40:47.250 
>>>>8           9307        15          NULL        30                         2009-07-27 15:28:54.970 
>>>>

>>>>
>>>>A user at a warehouse has shipped a part, PartId 9307, to warehouse 15. The first two records are the part being shipped to the warehouse and it being received and placed on location 199. An inventory record with no LocationId indicates a part shipped to a warehouse but has not yet been received. When the part is assigned a location, it is received.
>>>>
>>>>The third row is a second occurance of the same part shipped to the same warehouse. This is the change that I now must accomodate. The user can now ship the same part many times. If the SerialNo is used, then it's unique part, but with no serial no, it can be duplicate.
>>>>
>>>>So, first I think what I need is some sort of batch Id, so that I can tell the different occurances apart. Next, I need a way to pull only
>>>>the records that have not been received.
>>>>
>>>>I could use some help on the query.
>>>
>>>Can you post back the expected result?
>>>
>>>For now, just select * form WarehouseInfo where LocationID IS NULL
>>
>>That would return both records 7 & 8. The first occurance of the part is record 6 & 7. Record 6 is when it was sent, 7 is when it was received, so the only part not yes received is record 8.
>
>Is it because we have NULL in location ID and this is the latest record?
>
>In other words, we need to select all latest records where we have NULL in location ID, correct? (grouped by PartID, WarehouseID).
>
>If this is correct, I need few minutes to think.

Consider this..

I added a BatchId column:

InventoryId BatchId     PartId      WarehouseId LocationId  UserId      SerialNo       ActionDate              
----------- ----------- ----------- ----------- ----------- ----------- -------------- ----------------------- 
7           1           9307        15          NULL        36                         2009-07-27 13:48:17.767 
6           1           9307        15          199         36                    	   2009-07-27 14:40:47.250 
8           2           9307        15          NULL        30                         2009-07-27 15:28:54.970 

Notice that the second record for batch #1 has a location, so that instance of part 9307, records 7 & 6, should never show up as not received. That instance of the part has been received into location 199.

BatchId 2's record has not yet been received and should be returned by the query.