Selecting closest, non expired value

Question

I'm trying to select multiple rows where the value is closest to the end date but not passed, and return only those rows. Here's a sample of some data:

School Code | School Year | Semester | Term | Term End
========================================================
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

What I've done in my test case was:

SELECT TOP 1 * FROM School_Terms
WHERE Term_End >= '2015-01-20'

The problem is this only returns one row and not the first valid row for each school.

My expected results for the term end date '2015-01-20' are:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  1   | 2015-01-23
DEMO        | 20142015    |    1     |  1   | 2015-01-23

and if the Term End was '2015-01-30' I would expect:

School Code | School Year | Semester | Term | Term End
========================================================
EXAMPLE     | 20142015    |    1     |  2   | 2015-06-20
DEMO        | 20142015    |    1     |  2   | 2015-06-20

score 1 · Accepted Answer · answered Aug 20 '15 at 00:22

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 >= '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.

Selecting closest, non expired value

1 Answers1