SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [Student Enrollment]
    ([STUDENT] int, [COURSE] varchar(7), [ENROLL_DT] datetime)
;
    
INSERT INTO [Student Enrollment]
    ([STUDENT], [COURSE], [ENROLL_DT])
VALUES
    (11223, 'MATH123', '2017-01-03 01:00:00'),
    (11223, 'AUTO224', '2017-03-11 01:00:00'),
    (11223, 'FABR450', '2018-09-26 02:00:00')
;
3 rows affected

CREATE TABLE [Student Track]
    ([STUDENT] int, [TRACK] varchar(8), [DECLARE_DT] datetime, [STATUS] varchar(6))
;
    
INSERT INTO [Student Track]
    ([STUDENT], [TRACK], [DECLARE_DT], [STATUS])
VALUES
    (11223, 'MECH-AAS', '2016-12-04 01:00:00', 'Active'),
    (11223, 'MECH-AAS', '2016-02-05 01:00:00', 'Discon'),
    (11223, 'ENGR-AAS', '2017-02-20 01:00:00', 'Active')
;
3 rows affected

WITH CTE_track as (SELECT
  [STUDENT],[TRACK],  [DECLARE_DT], ROW_NUMBER() OVER(PARTITION BY STUDENT ORDER BY [DECLARE_DT]) rn
  FROM
[Student Track]),
CTE_enroll as (SELECT
  [STUDENT], [COURSE], [ENROLL_DT], ROW_NUMBER() OVER(PARTITION BY STUDENT ORDER BY [ENROLL_DT]) rn
  FROM
[Student Enrollment])
SELECT
c1.[STUDENT],c1.[COURSE],c1.[ENROLL_DT],c2.[TRACK]
FROM  CTE_enroll c1 LEFT JOIN CTE_track c2  ON c1.[STUDENT] = c2.[STUDENT] AND c1.rn = c2.rn


STUDENT
COURSE
ENROLL_DT
TRACK


11223
MATH123
2017-01-03 01:00:00.000
MECH-AAS

11223
AUTO224
2017-03-11 01:00:00.000
MECH-AAS

11223
FABR450
2018-09-26 02:00:00.000
ENGR-AAS

STUDENT	COURSE	ENROLL_DT	TRACK
11223	MATH123	2017-01-03 01:00:00.000	MECH-AAS
11223	AUTO224	2017-03-11 01:00:00.000	MECH-AAS
11223	FABR450	2018-09-26 02:00:00.000	ENGR-AAS