# 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 < 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) < 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) < 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) < 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) < 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) < CAST(GETDATE() AS DATE)

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