Query to get the "most recent" joins to another table with a date

Question

I have a couple of tables and you need to join them, but with a twist.

The #GradeChange table has student IDs, the effective date of the grade change, and the class in which they were changed to include this date. The #EventOccurrence table has events that occurred for this student on a specific date. I need to find in which class the student participated when the event occurred. This will be the very last class from #GradeChange that occurred before the #EventOccurrence Effective Date. Students may have multiple EventOccurrences, and we can assume that all students will have at least one #GradeChange entry with a date before the oldest event.

This is DDL:

/* If the test table already exists, drop it */ IF OBJECT_ID('TempDB..#GradeChange','U') IS NOT NULL DROP TABLE #GradeChange; IF OBJECT_ID('TempDB..#EventOccurrence','U') IS NOT NULL DROP TABLE #EventOccurrence; /* Create first temp table */ CREATE TABLE #GradeChange ( ID varchar(6), EffectiveDate datetime, Grade varchar(50) ); /* Populate it */ INSERT INTO #GradeChange (ID, EffectiveDate, Grade) SELECT '678443','Jul 2 2009 11:30PM','Grade 3' UNION ALL SELECT '678443','Jan 24 2007 2:40PM','Kindergarten - Half Day' UNION ALL SELECT '678443','Jul 4 2007 11:09PM','Grade 1' UNION ALL SELECT '678443','Jul 2 2008 11:35PM','Grade 2' UNION ALL SELECT '718466','May 18 2009 11:50PM','Pre-Kindergarten' UNION ALL SELECT '718466','Jul 2 2009 11:27PM','Kindergarten - Half Day' UNION ALL SELECT '718466','Aug 27 2009 11:18PM','Pre-Kindergarten' UNION ALL SELECT '718466','Jul 9 2010 11:18PM','Kindergarten - Half Day' UNION ALL SELECT '718466','Aug 2 2010 11:14PM','Kindergarten'; /* Create 2nd temp table */ CREATE TABLE #EventOccurrence ( ID varchar(6), EventDate datetime ); /* Populate it */ INSERT INTO #EventOccurrence (ID, EventDate) SELECT '718466','Nov 16 2010 12:00AM' UNION ALL SELECT '718466','May 20 2009 12:00AM' UNION ALL SELECT '678443','Dec 7 2007 12:00AM'; 

So, two tables will look like this:

And the expected results will look like this:

I played with "MAX" and "MIN" and "OVER ()", but did not quite understand. I really appreciate any help!

Answer 1

 select eo.ID, eo.EventDate, gc.Grade from #EventOccurrence eo inner join #GradeChange gc on eo.ID = gc.ID and gc.EffectiveDate = (select max(gc.EffectiveDate) from #GradeChange gc where gc.ID = eo.ID and gc.EffectiveDate <= eo.EventDate) 

Answer 2

 SELECT * FROM #EventOccurrence eo OUTER APPLY ( SELECT TOP 1 Grade FROM #GradeChange gc WHERE gc.id = eo.id AND gc.EffectiveDate <= eo.EventDate ORDER BY gc.EffectiveDate DESC ) gc 

Answer 3

 with merged (ID, DDate, Grade, pos) AS ( select ID, DDate, Grade, ROW_NUMBER() over (order by ID, DDate) AS pos FROM ( select ID, EffectiveDate AS DDate, Grade FROM GradeChange union select ID, EventDate AS DDate, NULL FROM EventOccurrence ) sub ) SELECT m1.ID, m1.DDate, m2.Grade FROM merged m1 LEFT OUTER JOIN merged m2 ON m1.pos = m2.pos+1 WHERE m1.Grade IS NULL