The solution is going to look very different because they changed the requirements on me a bit. Here's the final code:

WITH ctePLC (cKitID, cStdBatteryName)

AS

(

SELECT KitID, StdBatteryName

FROM (

SELECT KitId

,StdBatteryName

,COUNT(DISTINCT StdBatteryId) AS bCount

FROM PRE_LOAD

GROUP BY KitId, StdBatteryName

) AS t2

WHERE t2.bCount > 1

)

,

ctePLD (dKitID, dStdBatteryName)

AS

(

SELECT DISTINCT t1.KitID, t1.StdBatteryName FROM PRE_LOAD AS t1

INNER JOIN ctePLC

ON t1.KitID = ctePLC.cKitID

AND t1.StdBatteryName = ctePLC.cStdBatteryName

AND t1.TestStatus = 'D'

)

,

ctePLN (nKitID, nStdBatteryName)

AS

(

SELECT DISTINCT t1.KitID, t1.StdBatteryName FROM PRE_LOAD AS t1

INNER JOIN ctePLC

ON t1.KitID = ctePLC.cKitID

AND t1.StdBatteryName = ctePLC.cStdBatteryName

AND t1.TestStatus = 'N'

)

,

ctePLX (xKitID, xStdBatteryName)

AS

(

SELECT DISTINCT t1.KitID, t1.StdBatteryName FROM PRE_LOAD AS t1

INNER JOIN ctePLC

ON t1.KitID = ctePLC.cKitID

AND t1.StdBatteryName = ctePLC.cStdBatteryName

AND t1.TestStatus = 'X'

)

SELECT *

FROM PRE_LOAD AS t1

INNER JOIN ctePLC AS c

ON t1.KitID = c.cKitID

AND t1.StdBatteryName = c.cStdBatteryName

LEFT JOIN ctePLD AS d

ON c.cKitID = d.dKitID

AND c.cStdBatteryName = d.dStdBatteryName

LEFT JOIN ctePLN AS n

ON c.cKitID = n.nKitID

AND c.cStdBatteryName = n.nStdBatteryName

LEFT JOIN ctePLX AS x

ON c.cKitID = x.xKitID

AND c.cStdBatteryName = x.xStdBatteryName

WHERE

t1.TestStatus = 'N'

AND (

EXISTS (

SELECT t1.KitID, t1.StdBatteryName

FROM PRE_LOAD AS t1

WHERE t1.KitID = d.dKitID

AND t1.StdBatteryName = d.dStdBatteryName

)

OR

EXISTS (

SELECT t1.KitID, t1.StdBatteryName

FROM PRE_LOAD AS t1

WHERE t1.KitID = x.xKitID

AND t1.StdBatteryName = x.xStdBatteryName

)

)

OR

t1.TestStatus = 'X'

AND (

EXISTS (

SELECT t1.KitID, t1.StdBatteryName

FROM PRE_LOAD AS t1

WHERE t1.KitID = d.dKitID

AND t1.StdBatteryName = d.dStdBatteryName

)

AND

NOT EXISTS (

SELECT t1.KitID, t1.StdBatteryName

FROM PRE_LOAD AS t1

WHERE t1.KitID = n.nKitID

AND t1.StdBatteryName = n.nStdBatteryName

)

)

ORDER BY t1.KitID, t1.stdbatteryname,t1.stdbatteryid,t1.TestStatus

It selects all batteries under the same KitID where the TestStatus is 'N' and there exists a battery with the same name under that same KitID with a TestStatus of 'D' or 'X', and all batteries under the same KitID where the TestStatus is 'X' and there exists a battery with the same name under that same KitID with a TestStatus of 'D' but NO battery with the same name with a TestStatus of 'N'.

The ultimate goal is to delete all 'N' if there's a 'D' or an 'X' and all 'X' if there's a 'D' but no 'N'. I'm having trouble getting my DELETE statement to work, but I'll post that as a new thread.

Again, thanks to all!