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.