August 25, 2014 at 2:25 pm
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 for their help!
August 25, 2014 at 2:35 pm
Can you post up your solution when you have it tuned thanks.
August 26, 2014 at 9:42 am
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!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply