Quick thoughts, this is quite a different problem from the one you originally posted, can I ask why you didn't post the full problem up front? This way you are wasting everyone's time.

Further, the solution you posted is not very efficient, lots of unnecessary IO, table scans and is far too complicated for such a simple task.

😎

Here is a quick suggestion towards a solution, cannot say anything else as I don't know if the problem we are trying to solve is going to change yet again.

USE TEEST;

GO

SET NOCOUNT ON;

--/*

IF OBJECT_ID(N'dbo.TranscriptCourse') IS NOT NULL DROP TABLE dbo.TranscriptCourse;

CREATE TABLE dbo.TranscriptCourse

(

transcriptID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TranscriptCourse_transcriptID PRIMARY KEY CLUSTERED

,personID int NOT NULL

,startYear smallint NULL

,endYear smallint NULL

,grade CHAR(2) NULL

)

IF OBJECT_ID(N'dbo.Graduation') IS NOT NULL DROP TABLE dbo.Graduation;

CREATE TABLE dbo.Graduation

(

GraduationID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_GRADUATION_GRADUATIONID PRIMARY KEY CLUSTERED

,personID int NOT NULL

,gradyear INT NULL

)

-- SAMPLE DATA GENERATION dbo.TranscriptCourse

;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))AS X(N))

, NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

,PERSON_AND_START_YEAR AS

(

SELECT

ABS(CHECKSUM(NEWID())) % 1000 AS PERSON_ID

,(ABS(CHECKSUM(NEWID())) % 100) + 1950 AS START_YEAR

,CONVERT(VARCHAR(2),(ABS(CHECKSUM(NEWID())) % 12) + 1,0) AS GRADE

FROM NUMS NM

)

INSERT INTO dbo.TranscriptCourse(personID,startYear,endYear,grade)

SELECT

PASY.PERSON_ID

,PASY.START_YEAR

,PASY.START_YEAR + (ABS(CHECKSUM(NEWID())) % 100) AS END_YEAR

,STUFF('00',3 - LEN(PASY.GRADE),LEN(PASY.GRADE),PASY.GRADE) AS GRADE

FROM PERSON_AND_START_YEAR PASY;

-- SAMPLE DATA GENERATION dbo.Graduation

;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))AS X(N))

, NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

,PERSON_GRADE AS

(

SELECT

ABS(CHECKSUM(NEWID())) % 1000 AS PERSON_ID

,CONVERT(VARCHAR(2),(ABS(CHECKSUM(NEWID())) % 12) + 1,0) AS GRADE

FROM NUMS NM

)

INSERT INTO dbo.Graduation(personID,gradyear)

SELECT

PEGR.PERSON_ID

,(ABS(CHECKSUM(NEWID())) % 100) + 1954 AS GRAD_YEAR

FROM PERSON_GRADE PEGR;

CREATE NONCLUSTERED INDEX NCLIDX_DBO_TRANSCRIPTCOURSE_ENDYEAR_GRADE_INCL_PERSONID ON dbo.TranscriptCourse(endYear ASC,grade ASC) INCLUDE (personID);

CREATE NONCLUSTERED INDEX NCLIDX_DBO_GRADUATION_GRADYEAR_PERSONID ON dbo.Graduation(gradyear ASC,personID);

--*/

declare @gradYear int = 2017

RAISERROR ('CTE SOLUTION',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

;WITH EXCLUSION_SET AS

(

SELECT

TC.personID

FROM dbo.TranscriptCourse TC

WHERE TC.endYear = @gradYear - 4

AND TC.grade IN ('09','10','11','12')

)

SELECT

GR.personID

FROM dbo.Graduation GR

WHERE GR.gradyear = @gradYear

AND GR.personID NOT IN (SELECT ES.personID FROM EXCLUSION_SET ES)

GROUP BY GR.personID;

SET STATISTICS TIME,IO OFF;

RAISERROR ('TEMP TABLE SOLUTION',0,0) WITH NOWAIT;

SET STATISTICS IO,TIME ON;

SELECT distinct graduation.personID

into #gradPersonID

FROM dbo.Graduation graduation

where graduation.gradyear = @gradYear

SELECT distinct personID

FROM #gradPersonID graduation

where personID NOT in

(select distinct TranscriptCourse.personID from #gradPersonID graduation

join dbo.TranscriptCourse TranscriptCourse

on TranscriptCourse.personID = graduation.personID

where TranscriptCourse.grade between '09' and '12'

and @gradYear - 4 = TranscriptCourse.endYear)

DROP table #gradPersonID

SET STATISTICS TIME,IO OFF;

Statistics for the CTE query

CTE SOLUTION

Table 'Graduation'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TranscriptCourse'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 16 ms, elapsed time = 1 ms.

Statistics for the temp table query

TEMP TABLE SOLUTION

SQL Server parse and compile time: CPU time = 8 ms, elapsed time = 8 ms.

Table 'Graduation'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 4 ms.

SQL Server parse and compile time: CPU time = 44 ms, elapsed time = 44 ms.

Table '#gradPersonID_______________________________________________________________________________________________________00000000000D'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TranscriptCourse'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms.