Jeff Moden (5/22/2015)

---

Heh... you couldn't do it now, could you? You need to...

I dream of the day they upgrade to 2012 here...

Jason - that did it perfectly. Many thanks, if you're ever in Rochester, NY I owe you a beer (or other beverage of your choice as long as it doesn't...

8 views and no answers - is my question really hard or really stupid? 😉

Final working code, with huge thanks to Ken McKelvey:

WITH DeleteTests

AS

(

SELECT *

,MAX(CASE WHEN TestStatus = 'D' THEN 1 ELSE 0 END) OVER (PARTITION BY KitId, StdBatteryName) AS IsD

,MAX(CASE WHEN TestStatus =...

OK, I'll provide 50 rows of test data for those who don't mind creating a 62-column table:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PRE_LOAD') AND type in...

Ken McKelvey (8/26/2014)

---

To get a good answer you should really take the time to post test data in a comsumable format (CREATE TABLE, INSERT) and the expected results.

Your code looks...

Nope. That deleted the rows I wanted to keep and left the ones I wanted to delete. Fortunately I ran it against test data.

I want to KEEP PL1. I want...

So do I need to create a table, name it PL2, and stuff the result from the query into that table so I can then do the delete? I'm at...

Luis Cazares (8/26/2014)

---

I guess there's a misunderstanding.

You want to delete rows from PL1 when the data is in PL2.

You can't delete rows from a query, you delete rows from a...

It's trying to delete PL2 from PL1. PL2 is the result of the query that uses the CTEs. Is there another way to handle this delete that anyone can suggest?

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 ...

Actually Luis, yours gives the closest to the needed result on the actual data. With some tuning I think it's going to work for me. Thanks to everyone...

Eirikur, with this test data the expected result set would be:

C1473045850 T1954 3730 ''

C1473045850 T1954 3731 ''

C1473045850 T1954 3732 ''

C1473045850 ...

rxm119528 - no, all the rows where BatteryId = 'T1878' would be excluded because there is another BatteryId (T1954) where LBSTAT <> 'NOT DONE'.