Extract Students with All Enrolments in the Past I am trying to extract the students where all of their enrolments are in the Past.  


Some students can have more than one Enrolment and one of its End Date is in the future while the other one is in the past




In the above Sample Data, I want to get only one Record 'BBB' in my result.

I managed to achieve it by using CTE by comparing no. of Total Records vs no. of Total Records in the Past.  

WITH T1 AS
(SELECT
    RefNo
    , Total = COUNT(RecordID)
    , TotalPastRecords = SUM(IIF(EndDate &lt; GETDATE(), 1, 0))
FROM
    Enrolments
GROUP BY
    RefNo)
SELECT
  *
FROM
  T1
WHERE
  Total = TotalPastRecords


Could you guy please review the above code and let me know if there is a way to improve it?

Our Enrolment Table contains around 200,000 rows and this query is going to join to another 4-5 tables to get the final Result.
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