Condition in join aggregate function requires value from table that is out of scope

Question

Here are the details of the issue...

Table 1: [Student Enrollment]

STUDENT	COURSE	ENROLL_DT
11223	MATH123	01/03/2017
11223	AUTO224	03/11/2017
11223	FABR450	09/26/2018

Table 2: [Student Track]

STUDENT	TRACK	DECLARE_DT	STATUS
11223	MECH-AAS	12/04/2016	Active
11223	MECH-AAS	02/05/2016	Discon
11223	ENGR-AAS	02/20/2017	Active

What I'm trying to do:

Get the most recent active declared track at the time of enrollment.

STUDENT	COURSE	ENROLL_DT	TRACK
11223	MATH123	01/03/2017	MECH-AAS
11223	AUTO224	03/11/2017	ENGR-AAS
11223	FABR450	09/26/2018	ENGR-AAS

What I've tried with no avail:

• Inner join [Student Track] to itself to get max declare date for the student
• Left join the most recent active track at the time of enrollment to the [Student Enrollment] table

Code:

SELECT  
    STUDENT, COURSE, ENROLL_DT, TRACK
FROM
    [Student Enrollment] AS A
LEFT JOIN 
    (SELECT 
         STUDENT, TRACK, DECLARE_DT
     FROM 
         [Student Track] AS B1
     INNER JOIN 
          (SELECT STUDENT, MAX(DECLARE_DT) as MAX_DECLARE_DT
           FROM [Student Track]
           WHERE DECLARE_DT <= A.ENROLL_DT ***/* ENROLL_DT is out of scope!!!!!!!!!!!!!!!! =( */***
          ) as B2 ON A.STUDENT = B.STUDENT
WHERE 
    STATUS = 'Active'

Answer 1

You can use an APPLY for this, which allows you to access values from the outer scope.

Note that the join condition is moved to inside the subquery, because the subquery is (logically) exceuted once per row of the outer scope.

SELECT  
  se.STUDENT,
  se.COURSE,
  se.ENROLL_DT,
  st.TRACK
FROM
  [Student Enrollment] AS se
OUTER APPLY (
    SELECT TOP (1)
      st.TRACK,
      st.DECLARE_DT
    FROM 
      [Student Track] AS st
    WHERE st.DECLARE_DT <= se.ENROLL_DT
      AND st.STATUS = 'Active'
      AND st.STUDENT = se.STUDENT  -- join condition
    ORDER BY
      st.DECLARE_DT DESC
) AS st;

db<>fiddle