>>I have a table that contains shipping and receiving info for parts. The first record is the shipping record. Subsequent records are receipt records:
>>
>>

>>
>>This part has been shipped from location 1, received at location 152, the moved to 
>>locations 565 and 202
>>
>>RecId PartNo	LocationId	SerialNo        Qty		Date	                Sequence
>>-----------------------------------------------------------------------------------
>>5228	 17908	1		HUB5K043NC	1		NULL			1
>>5229	 17908	152		HUB5K043NC	1		2009-01-22 00:00:00.000	2
>>5230	 17908	565		HUB5K043NC	1		2009-02-02 00:00:00.000	3
>>5231	 17908	202		HUB5K043NC	1		2009-02-02 00:00:00.000	4
>>
>>
>>
>>This part has been shipped from location 1, but not yet received
>>
>>RecId PartNo	LocationId	SerialNo	 Qty		Date	                       Sequence
>>-----------------------------------------------------------------------------------
>>5953	16577	1		3BV0GW03	1		2009-04-01 00:00:00.000	1
>>
>>

>>
>>The combination of PartNo and SerialNo make a record unique. How do I pull only records that have not yet been received?
>
>There is probably a way to do it, using a subquery, but I can't see what it is. I don't understand why the first record (recid 5228) has a null date field and the last (5953) has a non-null date. Don't they both record the shipment of a part from some location? Or why records 5229, 5230, and 5231 are shipments which have been received yet look just like record 5953, which has been shipped but not received. The data model is really making it tough for you. There should be separate dates, shipped date and received date, instead of a generic field called Date (not a good idea itself) which is called upon to do double duty. Personally I would store shipments and receipts in separate tables, which would also make the job straightforward.



I agree that the data model isn't that great. I was given an Excel file and have imported the data into a SQL table.

The question is - How do I pull records where the combination of Part # and Sequence # only has one Sequence? That's the logic, I just don't know the syntax.