Extract Students with All Enrolments in the Past
Well, since you said that you are going to join to some other tables it seems like you only need the RefNo that meets your criteria. You can use HAVING and remove the CTE all together.

DECLARE @Enrollments TABLE(RecordID int, RefNo char(3), EndDate datetime)
INSERT INTO @Enrollments VALUES
(1,'AAA','1/1/2018'),
(2,'AAA','1/2/2018'),
(3,'BBB','1/1/2016'),
(4,'BBB','1/5/2016'),
(5,'CCC','1/5/2016'),
(6,'CCC','1/1/2018')


--Get the RefNo's that don't have a future date
SELECT
    RefNo
FROM
    @Enrollments
GROUP BY 
    RefNo
HAVING MAX(EndDate) &lt; CAST(GETDATE() AS DATE)

--Get all rows for RefNo's that don't have a future date
SELECT
    RecordID
    ,RefNo
    ,EndDate
FROM
    @Enrollments
WHERE 
    RefNo IN(
             SELECT RefNo 
             FROM @Enrollments 
             GROUP BY RefNo 
             HAVING MAX(EndDate) &lt; CAST(GETDATE() AS DATE)
             )


If you are just needing the RefNo's to limit results of another table. Then you can use the first query with IN similarly to how the second query is written.

You can see the execution plan here comparing the CTE method you have compared to the sub-query I provided in the second query. On my instance, the plan broke down as follows:

Query Cost (Relative to the Batch)
INSERT INTO @Enrollments....24%
SUBQUERY Example using IN...16%
INSERT INTO @Enrollments....24%
Query with CTE Example......36%


So, the CTE was slower. Granted a lot is going to come into play in your live environment with indexes, what joins you are doing etc. But at least this is another option you can consider.

Lastly, depending on your indexes and joins it's sometimes faster to bring stage a temporary result set in a CTE, Table Variable or Temp Table. In most of my cases, Temp Tables have been the fastest but this could be different for your environment. You already have the CTE method down, though I would do it slightly different. Here are some examples.

--stage the RefNo in a temp table
IF OBJECT_ID('tempdb..#RefNo') IS NOT NULL DROP TABLE #RefNo

SELECT
    RefNo
INTO #RefNo
FROM
    @Enrollments
GROUP BY 
    RefNo
HAVING MAX(EndDate) &lt; CAST(GETDATE() AS DATE)

SELECT
    ...
FROM
    YourTable t
    INNER JOIN
    #RefNo n on t.RefNo = n.RefNo



--use a CTE to limit the RefNo. Understand the optimizer may not logically execute this how it is written
WITH cte AS(
    SELECT
        RefNo
    FROM
        @Enrollments
    GROUP BY 
        RefNo
    HAVING MAX(EndDate) &lt; CAST(GETDATE() AS DATE))

SELECT
    ...
FROM
    YourTable t
    INNER JOIN
    cte c on t.RefNo = c.RefNo


--using a table variable, which I wouldn't do in most cases
DECLARE @RefNo TABLE (RefNo CHAR(3))

INSERT INTO @RefNo
    SELECT
        RefNo
    FROM
        @Enrollments
    GROUP BY 
        RefNo
    HAVING MAX(EndDate) &lt; CAST(GETDATE() AS DATE)

SELECT
    ...
FROM
    YourTable t
    INNER JOIN
    @RefNo rn on t.RefNo = rn.RefNo