January 5, 2016 at 11:03 am
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!
January 5, 2016 at 1:26 pm
January 5, 2016 at 4:26 pm
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
January 5, 2016 at 4:57 pm
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/.
January 7, 2016 at 8:46 am
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)
January 7, 2016 at 9:05 am
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?
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
January 7, 2016 at 9:17 am
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?
January 7, 2016 at 2:33 pm
Magy (1/7/2016)
productionStarted should be tr.productionStartedpartID 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
January 11, 2016 at 10:30 am
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".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply