Selecting closest, non expired value
I think this is a situation where you just need a cross apply.  If you are not familiar with it, look at this link.

With your data I created a table and inserted the data with this scrip

CREATE TABLE school
(
    schoolcode VARCHAR(20)
    ,SchoolYear INT
    ,Semester INT
    ,Term INT
    ,TermEnd DATETIME2

);

INSERT INTO dbo.school
        (
          schoolcode,
          SchoolYear,
          Semester,
          Term,
          TermEnd
        )
VALUES  ('EXAMPLE', 20142015, 1, 1, '2015-01-23')
, ('EXAMPLE', 20142015, 1, 2, '2015-06-20')
, ('DEMO', 20142015, 1, 1, '2015-01-23')
, ('DEMO', 20142015, 1, 2, '2015-06-20');


And then I run the select to get your desired results in the following fashion

SELECT DISTINCT t.*
FROM School s
CROSS APPLY (SELECT TOP 1  *
                FROM school s1
                WHERE s1.schoolcode = s.schoolcode
                AND s1.SchoolYear = s.SchoolYear
                AND TermEnd &gt;= '2015-01-20'
                ORDER BY TermEnd) t


If you change your date, you should continue to get those desired results.

The APPLY operator can be your friend.