Finding the last record within three years of the first record with subqueries I have the following table with student enrollment data: 

CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO


I want to 


Find the first year that the student was enrolled in a program.
Find the latest enrollment from all the enrollments occurring within 3 years of the first year.   
Get the DepartureCode for the enrollment in 2.


Output for sample data above would be:

+--------------+--------------+--------------+--------------+---------------+
| SequenceID   | SchoolYear   | StudentID    | ProgramID    | DepartureCode |
+--------------+--------------+--------------+--------------+---------------+
|     37105    |         2014 |     9800384  |         5331 |           01  |
|     37320    |         2014 |     9100926  |         5729 |            01 |
+--------------+--------------+--------------+--------------+---------------+


This is the full record for the last enrollment within 3 years of the first enrollment.

I'm doing this with subqueries as below, but wondering if there is another way that is easier to read?

SELECT
    MainQueryEnrollments .StudentID,
    MainQueryEnrollments .ProgramID,
    MainQueryEnrollments .SchoolYear,
    MainQueryEnrollments .DepartureCode
FROM
    Enrollments AS MainQueryEnrollments
JOIN
    ( -- find SequenceID of the last enrollment within 
      -- three years of the first enrollment
    SELECT
        SubQueryEnrollments.StudentID,
        SubQueryEnrollments.ProgramID,
        MAX(SubQueryEnrollments SequenceID) AS SequenceID
    FROM
        Enrollments AS SubQueryEnrollments
    JOIN
        ( -- find the SchoolYear of the first enrollment
          SELECT
            StudentID,
            ProgramID,
            MIN(SchoolYear) AS SchoolYear
        FROM Enrollments
        GROUP BY
            StudentID,
            ProgramID
        ) FirstEnrollment
    ON FirstEnrollment.StudentID = SubQueryEnrollments.StudentID
    AND FirstEnrollment.ProgramID = SubQueryEnrollments.ProgramID
    AND SubQueryEnrollments.SchoolYear - FirstEnrollment.SchoolYear &lt;= 2  
      -- enrollments within three years of the first enrollment
    GROUP BY
        SubQueryEnrollments .StudentID,
        SubQueryEnrollments .ProgramID
) AS LastEnrollment3Years
ON MainQueryEnrollments.SequenceID = LastEnrollment3Years.SequenceID
My solution makes the following assumptions (please let me know if I have misunderstood your requirements)


SequenceId is like an Identity value and represents the chronological order of student changes.
You want to find the enrollment row for each student where the SequenceId is the MAX(SequenceId) for each student where the SchoolYear is within 3 years of the initial enrollment year for that student.




SET NOCOUNT ON
DROP TABLE IF EXISTS [dbo].[Enrollments];
CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO

;

WITH YearFirstEnrolled
AS (
    SELECT StudentID
        ,min(SchoolYear) AS YearFirstEnrolled
    FROM Enrollments
    GROUP BY StudentID
    )
SELECT *
FROM YearFirstEnrolled fe
JOIN Enrollments e
    ON e.StudentID = fe.StudentID
        AND e.SequenceID = (
            SELECT max(SequenceID)
            FROM Enrollments
            WHERE StudentID = e.StudentID
                AND SchoolYear &lt; fe.YearFirstEnrolled + 3
            )
order by e.SequenceID




| StudentID | YearFirstEnrolled | SequenceID | SchoolYear | StudentID | ProgramID | DepartureCode |
|-----------|-------------------|------------|------------|-----------|-----------|---------------|
| 9800384   | 2012              | 37105      | 2014       | 9800384   | 5331      | 01            |
| 9100926   | 2012              | 37320      | 2014       | 9100926   | 5729      | 01            |




Select your desired columns from the result.