Here's an extended set of sample data and the solutions posted. I guess Micky's got the correct formula.

CREATE TABLE PRE_LOAD(

KitID varchar(20) NULL,

BatteryID varchar(20) NULL,

TestID varchar(20) NULL,

LBSTAT varchar(8) NULL

)

INSERT INTO PRE_LOAD

(KitID, BatteryID, TestID, LBSTAT)

SELECT 'C1473999999','T5555','3730','NOT DONE' UNION ALL

SELECT 'C1473999999','T5555','3731','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3730','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3731','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3732','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3733','NOT DONE' UNION ALL

SELECT 'C1473045850','T1954','3730','' UNION ALL

SELECT 'C1473045850','T1954','3731','' UNION ALL

SELECT 'C1473045850','T1954','3732','' UNION ALL

SELECT 'C1473045850','T1954','3733','' UNION ALL

SELECT 'C1111111111','T1234','3737','' UNION ALL

SELECT 'C1111111111','T1234','3737','' UNION ALL

SELECT 'C1111111111','T4444','3738',''

select *

from PRE_LOAD

where BatteryID not in

(

select BatteryID

from PRE_LOAD

where LBSTAT = 'NOT DONE'

)

select *

from PRE_LOAD ex

where EXISTS

(

select 1

from PRE_LOAD i

where LBSTAT <> 'NOT DONE'

AND ex.kitID = i.KitID

)

SELECT p.KitID,

p.BatteryID,

p.TestID,

p.LBSTAT

FROM PRE_LOAD p

CROSS APPLY (

SELECT COUNT(*) chk

FROM PRE_LOAD c

WHERE p.KitID = c.KitID AND

p.TestID = c.TestID AND

c.LBSTAT <> 'NOT DONE'

) x

WHERE LBSTAT <> 'NOT DONE' OR

x.chk = 0

GO

DROP TABLE PRE_LOAD