Loop through an IN() clause?

Question

Loop through an IN() clause?

Magy

SSCarpal Tunnel

Points: 4217
More actions
January 5, 2016 at 11:03 am

#323701

I have to run this query for each @partId. It gives me a list of testers who didn't test a certain partId.
--Example partId
DECLARE @partId INT = 1234567890;
WITH testResults AS
(
SELECT ce.testerId, tr.testID, COUNT(*) as resultCount,
MIN(productionStarted)
OVER(
PARTITION BY tr.testerId) AS productionStarted
FROM dbo.catalogEntries ce
INNER JOIN testerResponses tr ON tr.testerId = ce.testerId
WHERE partID = @partId
GROUP BY ce.testerId, tr.testId, tr.testerId, productionStarted
), untested AS
(
SELECT ce.testerId, @partId as testId, 0 as resultCount, 'no implementation' as testStatus
FROM dbo.catalogEntries ce
WHERE ce.testerId NOT IN (SELECT testerId FROM testResults)
)
SELECT
partDisplayName, testerName, resultCount, testStatus
FROM untested u
LEFT JOIN partList pl ON u.testId = pl.partId
I would like to be able to do this in the 'testResults' CTE:
WHERE partID IN(1,2,3,4,5,6,7,8,9,10,11,12)
However, I can't find a work around for my 'untested' CTE where I use the specific @partId in the query.
Is there a way to do this? I suppose it would have to be some type of loop logic where it loops through all the partIds in the IN() clause?
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76989 More actions · Answer 1

Please post sample data and expected results as specified in Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sergiy SSC Guru Points: 110208 More actions · Answer 2

You do not need all those CTE's.

This simple query will do:

DECLARE @PartID_List VARCHAR(8000)

SET @PartID_List = '1,2,3,4,5,6,7,8,9,10,11,12'

SELECT pl.partDisplayName, pl.testerName, ce.testerId, partId as testId, 0 as resultCount, 'no implementation' as testStatus

FROM dbo.catalogEntries ce

LEFT JOIN partList pl ON u.testId = pl.partId

WHERE partID IN (SELECT Value FROM dbo.Split8k (@PartID_List) )

AND NOT EXISTS (SELECT * FROM testerResponses tr

WHERE tr.testerId = ce.testerId)

_____________
Code for TallyGenerator

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Sergiy (1/5/2016)
You do not need all those CTE's.
This simple query will do:
DECLARE @PartID_List VARCHAR(8000)
SET @PartID_List = '1,2,3,4,5,6,7,8,9,10,11,12'
SELECT pl.partDisplayName, pl.testerName, ce.testerId, partId as testId, 0 as resultCount, 'no implementation' as testStatus
FROM dbo.catalogEntries ce
LEFT JOIN partList pl ON u.testId = pl.partId
WHERE partID IN (SELECT Value FROM dbo.Split8k (@PartID_List) )
AND NOT EXISTS (SELECT * FROM testerResponses tr
WHERE tr.testerId = ce.testerId)

Nice, Sergiy. Magy, for the article that (I think) dbo.Split8K is built from, see http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 4

Thank you, but you are using 'u.testId' which comes from one of my CTEs.

Sergiy (1/5/2016)
You do not need all those CTE's.
This simple query will do:
DECLARE @PartID_List VARCHAR(8000)
SET @PartID_List = '1,2,3,4,5,6,7,8,9,10,11,12'
SELECT pl.partDisplayName, pl.testerName, ce.testerId, partId as testId, 0 as resultCount, 'no implementation' as testStatus
FROM dbo.catalogEntries ce
LEFT JOIN partList pl ON u.testId = pl.partId
WHERE partID IN (SELECT Value FROM dbo.Split8k (@PartID_List) )
AND NOT EXISTS (SELECT * FROM testerResponses tr
WHERE tr.testerId = ce.testerId)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

There are some code gotcha's in your original query. Looking at the first CTE, and expanding the range of PartID to get a decent sample set for testing, shouldn't this:

SELECT

ce.testerId,

tr.testID,

resultCount = COUNT(*),

productionStarted = MIN(productionStarted) OVER(PARTITION BY tr.testerId)

FROM dbo.catalogEntries ce

INNER JOIN testerResponses tr

ON tr.testerId = ce.testerId

WHERE partID BETWEEN 1 AND 12

GROUP BY ce.testerId, tr.testId, tr.testerId, productionStarted

Be this:

SELECT

tr.testerId,

tr.testID,

resultCount = COUNT(*),

productionStarted = MIN(productionStarted) --OVER(PARTITION BY tr.testerId)

FROM dbo.catalogEntries ce

INNER JOIN testerResponses tr

ON tr.testerId = ce.testerId

WHERE partID BETWEEN 1 AND 12

GROUP BY tr.testerId, tr.testId --, tr.testerId, productionStarted

- can you put in the table aliases for those columns where it's omitted please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 6

productionStarted should be tr.productionStarted

partID should be tr.partID

Thanks!

ChrisM@Work (1/7/2016)
There are some code gotcha's in your original query. Looking at the first CTE, and expanding the range of PartID to get a decent sample set for testing, shouldn't this:
SELECT
ce.testerId,
tr.testID,
resultCount = COUNT(*),
productionStarted = MIN(productionStarted) OVER(PARTITION BY tr.testerId)
FROM dbo.catalogEntries ce
INNER JOIN testerResponses tr
ON tr.testerId = ce.testerId
WHERE partID BETWEEN 1 AND 12
GROUP BY ce.testerId, tr.testId, tr.testerId, productionStarted
Be this:
SELECT
tr.testerId,
tr.testID,
resultCount = COUNT(*),
productionStarted = MIN(productionStarted) --OVER(PARTITION BY tr.testerId)
FROM dbo.catalogEntries ce
INNER JOIN testerResponses tr
ON tr.testerId = ce.testerId
WHERE partID BETWEEN 1 AND 12
GROUP BY tr.testerId, tr.testId --, tr.testerId, productionStarted
- can you put in the table aliases for those columns where it's omitted please?

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Magy (1/7/2016)
productionStarted should be tr.productionStarted
partID should be tr.partID
Thanks!

Hi Magy,

The main question is about you "untested" CTE:

SELECT ce.testerId, @partId as testId, 0 as resultCount, 'no implementation' as testStatus

FROM dbo.catalogEntries ce

WHERE ce.testerId NOT IN (SELECT testerId FROM testResults)

Is PartID mentioned somehow in the table dbo.catalogEntries ?

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101105 More actions · Answer 8

It seems you want to see a tester that has never performed a check on a given part id, not just for the first/last check.

If so, I think you could simplify the queries to something like below ... maybe?!

IF OBJECT_ID('tempdb.dbo.#partIds') IS NOT NULL

DROP TABLE #partIds

CREATE TABLE #partIds ( partId int PRIMARY KEY )

INSERT INTO #partIds VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

WITH testResults AS

(

SELECT DISTINCT tr.testerId, tr.partId

FROM testerResponses tr

INNER JOIN #partIds p ON p.partId = tr.PartId

), untested AS

(

SELECT ce.testerId, p.partId as testId, 0 as resultCount, 'no implementation' as testStatus

FROM dbo.catalogEntries ce

CROSS JOIN #partIds p

WHERE NOT EXISTS(SELECT 1 FROM testResults tr WHERE tr.testerId = ce.testerID AND tr.partID = p.partId)

)

SELECT

pl.partDisplayName, ??.testerName, u.resultCount, u.testStatus

FROM untested u

LEFT JOIN partList pl ON u.testId = pl.partId

Edit: Changed WHERE clause in first cte to an inner join, to match the temp table I created, vs. the original variable.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".