Loop through an IN() clause?

Question

Post reply

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: 76776 More actions · Answer 1

drew.allen

SSC Guru

Points: 76776

January 5, 2016 at 1:26 pm

#1849658

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: 110192 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: 287015 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://qa.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: 110192 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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!