I am glad you have a working solution however your test data is useless as it does not function as a test harness:

1. At a very basic level, it does not load due to there being more columns in the column list than there are in the SELECTs.

2. The test table is overly complex. Looking at your query the following should do:

CREATE TABLE #t

(

KitID varchar(20) NULL

,StdBatteryID varchar(20) NULL

,StdBatteryName varchar(40) NULL

,StdTestID varchar(20) NULL

,TestStatus varchar(13) NULL

);

3. Even if the data could be loaded, it does not look as though it will satisfy the conditions you are looking for.

eg It looks as though the following will produce no rows:

SELECT KitId, StdBatteryName

FROM PRE_LOAD

GROUP BY KitId, StdBatteryName

HAVING COUNT(DISTINCT StdBatteryId) > 1;

4. You have not shown what the test data should look like after it has been processed.

Looking at your final query, it looks as though it reduces to the following but without a cogent test harness it is difficult to tell:

WITH Tests

AS

(

SELECT KitId, StdBatteryName

,COUNT(DISTINCT StdBatteryId) AS IDCount

,MAX(CASE WHEN TestStatus = 'D' THEN 1 ELSE 0 END) AS IsD

,MAX(CASE WHEN TestStatus = 'N' THEN 1 ELSE 0 END) AS IsN

,MAX(CASE WHEN TestStatus = 'X' THEN 1 ELSE 0 END) AS IsX

FROM dbo.PRE_LOAD

GROUP BY KitId, StdBatteryName

)

DELETE P

FROM dbo.PRE_LOAD P

JOIN Tests T

ON P.KitId = T.KitID

AND P.StdBatteryName = T.StdBatteryName

WHERE IDCount > 1

AND

(

(P.TestStatus = 'N' AND (T.IsD = 1 OR T.IsX = 1))

OR (P.TestStatus = 'X' AND T.IsD = 1 AND T.IsN = 0)

);