Abfrage zum Abrufen der "aktuellsten" Verknüpfung mit einer anderen Tabelle mit Datum

Question

Ich habe mehrere Tabellen und muss sie zusammenführen, aber mit einem kleinen Kniff.

Die Tabelle #GradeChange enthält Schüler-IDs, das Datum des Inkrafttretens der Notenänderung und die Note, in die sie an diesem Datum geändert wurden. Die Tabelle #EventOccurrence enthält Ereignisse, die für diesen Schüler an einem bestimmten Datum eingetreten sind. Ich muss herausfinden, in welcher Klasse der Schüler war, als das Ereignis eintrat. Dies ist die letzte Note aus #GradeChange, die vor dem Gültigkeitsdatum von #EventOccurrence aufgetreten ist. Schüler können mehrere EventOccurrences haben, und wir können davon ausgehen, dass alle Schüler mindestens einen #GradeChange-Eintrag mit einem Datum haben, das vor ihrem ältesten Event liegt.

Dies ist die 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';

Die beiden Tabellen würden also wie folgt aussehen:

Die erwarteten Ergebnisse würden wie folgt aussehen:

Ich habe mit "MAX" und "MIN" und "OVER()" gespielt, aber ich bekomme es nicht ganz hin. Ich bin für jede Hilfe sehr dankbar!

Answer 1

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 2

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 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