August 26, 2014 at 9:54 am
I'm hoping someone can see why I'm getting an error:
Msg 4405, Level 16, State 1, Line 13
View or function 'PL2' is not updatable because the modification affects multiple base tables.
when trying to run this 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'
)
DELETE PL2
FROM dbo.PRE_LOAD PL1 JOIN (
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
)
)
) PL2
ON PL1.KitID = PL2.KitID
AND PL1.StdTestID = PL2.StdTestID
AND PL1.StdBatteryName = PL2.StdBatteryName
AND PL1.StdBatteryID <> PL2.StdBatteryID
As far as I can see, I'm only trying to delete from the PRE_LOAD table aliased as "PL1". Are the CTEs causing the issue? I need to delete the result set from the query that uses the CTEs from the PRE_LOAD table.
It would be difficult to provide test data because the PRE_LOAD table has 62 columns and 80,000 rows and there are numerous possibilities that would have to be represented, but if it's really necessary in order to spot the error in the logic I'll try to create a manageable test data set.
August 26, 2014 at 10:01 am
Actually, the code is trying to delete PL2 instead of PL1.
PL2 is not updateable, so you can delete from it.
August 26, 2014 at 10:16 am
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?
August 26, 2014 at 10:27 am
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 table.
Should I try to explain it in a different manner?
August 26, 2014 at 10:33 am
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 table.
Should I try to explain it in a different manner?
Exactly, I want to delete rows from PL1 when the data is in PL2.
I know I can't delete rows from a query, but PL1 is a table, not a query. PL2 is a query that I've given an alias. I'm not trying to delete anything from PL2. Is the "PL2" alias making SQL Server see PL2 as a table? If so, how can I delete the results from the PL2 query from the PRE_LOAD table (whether aliased as PL1 or not)?
August 26, 2014 at 10:39 am
The full syntax for DELETE should make this more clear:
DELETE FROM PL2
FROM dbo.PRE_LOAD PL1 JOIN (
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 26, 2014 at 10:48 am
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 work and this has been driving me crazy all morning, I really need to get it done.
August 26, 2014 at 10:50 am
jkalmar 43328 (8/26/2014)
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 table.
Should I try to explain it in a different manner?
Exactly, I want to delete rows from PL1 when the data is in PL2.
I know I can't delete rows from a query, but PL1 is a table, not a query. PL2 is a query that I've given an alias. I'm not trying to delete anything from PL2. Is the "PL2" alias making SQL Server see PL2 as a table? If so, how can I delete the results from the PL2 query from the PRE_LOAD table (whether aliased as PL1 or not)?
You need to do a very simple change to your DELETE.
DELETE PL1
FROM dbo.PRE_LOAD PL1 JOIN (
August 26, 2014 at 11:08 am
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 far too convoluted. At a guess I would try something like the following although without test data it is difficult to tell.
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 = 'N' THEN 1 ELSE 0 END) OVER (PARTITION BY KitId, StdBatteryName) AS IsN
,MAX(CASE WHEN TestStatus = 'X' THEN 1 ELSE 0 END) OVER (PARTITION BY KitId, StdBatteryName) AS IsX
FROM PRE_LOAD P
WHERE EXISTS
(
SELECT 1
FROM PRE_LOAD P1
WHERE P1.KitId = P.KitId AND P1.StdBatteryName = P.StdBatteryName
GROUP BY KitId, StdBatteryName
HAVING COUNT(DISTINCT StdBatteryId) > 1
)
)
DELETE DeleteTests
WHERE (TestStatus = 'N' AND (IsD = 1 OR IsX = 1))
OR (TestStatus = 'X' AND IsD = 1 AND IsN = 0);
August 26, 2014 at 11:34 am
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 to DELETE PL2.
EDIT: This was meant for Luis.
August 26, 2014 at 11:58 am
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 far too convoluted. At a guess I would try something like the following although without test data it is difficult to tell.
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 = 'N' THEN 1 ELSE 0 END) OVER (PARTITION BY KitId, StdBatteryName) AS IsN
,MAX(CASE WHEN TestStatus = 'X' THEN 1 ELSE 0 END) OVER (PARTITION BY KitId, StdBatteryName) AS IsX
FROM PRE_LOAD P
WHERE EXISTS
(
SELECT 1
FROM PRE_LOAD P1
WHERE P1.KitId = P.KitId AND P1.StdBatteryName = P.StdBatteryName
GROUP BY KitId, StdBatteryName
HAVING COUNT(DISTINCT StdBatteryId) > 1
)
)
DELETE DeleteTests
WHERE (TestStatus = 'N' AND (IsD = 1 OR IsX = 1))
OR (TestStatus = 'X' AND IsD = 1 AND IsN = 0);
Holy cow, that is much simpler and almost does what I need. The only issue is that it's setting IsD and IsN BOTH to 1 if ANY of the TestStatus rows are N or D. If I can sort that out this will do it!
August 26, 2014 at 12:21 pm
jkalmar 43328 (8/26/2014)
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 to DELETE PL2.
EDIT: This was meant for Luis.
You need one more adjustment to make luis' recommendation work - change the last join to be =, and not a <>
As in -change
AND PL1.StdBatteryID <> PL2.StdBatteryID
to
AND PL1.StdBatteryID = PL2.StdBatteryID
It will then find every row from PL1 which matches PL2 and delete them (which you previously stated was your requirement.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 26, 2014 at 1:08 pm
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 (N'U'))
DROP TABLE PRE_LOAD
GO
CREATE TABLE PRE_LOAD(
StdVisitID varchar(20) NULL,
StdVisitName varchar(40) NULL,
KitID varchar(20) NULL,
StdBatteryID varchar(20) NULL,
StdBatteryName varchar(40) NULL,
PerformingLabID varchar(20) NULL,
StdTestID varchar(20) NULL,
StdTestName varchar(100) NULL,
TestStatus varchar(13) NULL,
ReportedResultStatus varchar(11) NULL,
IsBlinded bit NULL,
FILCRDTC varchar(19) NULL,
LABNAM varchar(40) NULL,
DOMAIN varchar(2) NULL,
STUDYID varchar(20) NULL,
SITEID varchar(20) NULL,
SCRNID varchar(20) NULL,
SUBJID varchar(20) NULL,
SUBJINIT varchar(3) NULL,
SUBJSEX varchar(1) NULL,
SUBJDOB varchar(10) NULL,
SUBJAGE int NULL,
SUBJAGEU varchar(6) NULL,
VISITNUM varchar(20) NULL,
VISIT varchar(50) NULL,
LBFAST varchar(1) NULL,
LBTPT varchar(40) NULL,
LBTPTNUM float NULL,
LBACCNO varchar(20) NULL,
LBKITNO varchar(20) NULL,
LBCOLDTC varchar(19) NULL,
LBRECDTC varchar(19) NULL,
LBTSTDTC varchar(19) NULL,
LBREFID varchar(20) NULL,
LBSPEC varchar(40) NULL,
LBCATCD varchar(20) NULL,
LBCAT varchar(40) NULL,
LBTESTCD varchar(20) NULL,
LBTEST varchar(100) NULL,
LBSTAT varchar(8) NULL,
LBREASND varchar(200) NULL,
LBORRES varchar(200) NULL,
LBORRESU varchar(20) NULL,
LBORNRLO varchar(40) NULL,
LBORNRHI varchar(40) NULL,
LBNRIND varchar(14) NULL,
LBCVRESC varchar(200) NULL,
LBCVRESN float NULL,
LBCVRESU varchar(20) NULL,
LBCVNRLO float NULL,
LBCVNRHI float NULL,
LBSTRESC varchar(200) NULL,
LBSTRESN float NULL,
LBSTRESU varchar(20) NULL,
LBSTNRLO float NULL,
LBSTNRHI float NULL,
LBSTNRC varchar(40) NULL,
LBCOM1 varchar(200) NULL,
LBCOM2 varchar(200) NULL,
LBCOM3 varchar(200) NULL,
LBCOM4 varchar(200) NULL,
LBCOM5 varchar(200) NULL
)
INSERT INTO PRE_LOAD(StdVisitID,
StdVisitName,
KitID,
StdBatteryID,
StdBatteryName,
PerformingLabID,
StdTestID,
StdTestName,
TestStatus,
ReportedResultStatus,
IsBlinded,
FILCRDTC,
LABNAM,
DOMAIN,
STUDYID,
SITEID,
SCRNID,
SUBJID,
SUBJINIT,
SUBJSEX,
SUBJDOB,
SUBJAGE,
SUBJAGEU,
VISITNUM,
VISIT,
LBFAST,
LBTPT,
LBTPTNUM,
LBACCNO,
LBKITNO,
LBCOLDTC,
LBRECDTC,
LBTSTDTC,
LBREFID,
LBSPEC,
LBCATCD,
LBCAT,
LBTESTCD,
LBTEST,
LBSTAT,
LBREASND,
LBORRES,
LBORRESU,
LBORNRLO,
LBORNRHI,
LBNRIND,
LBCVRESC,
LBCVRESN,
LBCVRESU,
LBCVNRLO,
LBCVNRHI,
LBSTRESC,
LBSTRESN,
LBSTRESU,
LBSTNRLO,
LBSTNRHI,
LBSTNRC,
LBCOM1,
LBCOM2,
LBCOM3,
LBCOM4,
LBCOM5)
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3751','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3759','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3764','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3765','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3767','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3769','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3770','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','GF7C2','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3740','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3745','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3747','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3750','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3730','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3731','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3732','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3734','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3735','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3738','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1878','CHEMISTRY','XYZ','3739','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Serum','T1878','CHEMISTRY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','3781','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','N','1','4.85','10^6/uL','4.5','5.9','4.85','4.85','10^12/L','4.5','5.9' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','3782','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','N','1','14.3','g/dL','13','17.5','143','143','g/L','130','175' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','3783','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','N','1','44.4','%','41.6','54.1','0.444','0.444','L/L','0.416','0.541' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','3784','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','N','1','228','10^3/uL','130','400','228','228','10^9/L','130','400' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7ABC','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7AEC','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7ALC','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7AMC','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7ANC','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7BS%','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7CWBC','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','LN','1','2.3','10^3/uL','4.5','11','2.3','2.3','10^9/L','4.5','11' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7EO%','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7LY%','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7MCH','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','N','1','29.5','pg','26','34','29.5','29.5','pg','26','34' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7MCHC','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','N','1','32.2','g/dL','30.7','34.6','322','322','g/L','307','346' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7MCV','','D','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','','','1','uL','1','1','N','1','91.5','fL','83','104','92','92','fL','83','104' UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7MN%','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1879','HEMATOLOGY','XYZ','7NT%','','N','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00','2014-07-07T17:05:00','','Blood, Whole','T1879','HEMATOLOGY','1234','','NOT DONE','NOT ORDERED','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7APC','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7AUC','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7BAC','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7EPIR','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7EPIS','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7EPIT','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7UHC2','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7URBC','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','7UWBC','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','V7BIL','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','V7GLU','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','V7GRC','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','V7KET','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL UNION ALL
SELECT 'V180A','Visit 9','C1473045850','T1880','URINALYSIS','XYZ','V7LEU','','X','F','0','2014-08-26T13:50:22','XYZ Blah Blah Blah','LB','Clin301-201','10','2595688','10-006','ABC','M','41877','21','Y','V180A','Treatment Visit 9 Day 35 or Early Termination','Y','',NULL,'O7034939','C1473045850','2014-07-03T17:02:00','2014-07-07T10:50:00',NULL,'','Urine','T1880','URINALYSIS','1234','','NOT DONE','Cancelled - Specimen Age','1','uL','1','1','','1',NULL,'',NULL,NULL,'',NULL,'',NULL,NULL
August 26, 2014 at 1:31 pm
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 = 'N' THEN 1 ELSE 0 END) OVER (PARTITION BY KitId, StdBatteryName) AS IsN
,MAX(CASE WHEN TestStatus = 'X' THEN 1 ELSE 0 END) OVER (PARTITION BY KitId, StdBatteryName) AS IsX
FROM PRE_LOAD P
WHERE EXISTS
(
SELECT 1
FROM PRE_LOAD P1
WHERE P1.KitId = P.KitId AND P1.StdBatteryName = P.StdBatteryName
GROUP BY KitId, StdBatteryName
HAVING COUNT(DISTINCT StdBatteryId) > 1
)
)
DELETE FROM PL
FROM dbo.PRE_LOAD PL JOIN DeleteTests DT
ON PL.KitID = DT.KitID
AND PL.StdTestID = DT.StdTestID
AND PL.StdBatteryName = DT.StdBatteryName
AND PL.TestStatus = DT.TestStatus
AND (DT.TestStatus = 'N' AND (IsD = 1 OR IsX = 1))
OR (DT.TestStatus = 'X' AND DT.IsD = 1 AND DT.IsN = 0);
August 27, 2014 at 7:02 am
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)
);
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply