Get the first record from Table 2 where timestamp is greater than timestamp in Table 1

I have two tables.

EVENT_NARATIVE
EventNum
SEQ
Message
Message_Time

GRADE_HISTORY
EventNum
Grade
GradeChangeTime

The EVENT_NARATIVE Table has a lot of data (millions of rows) and each Event (EventNum) can have multiple EVENT_NARATIVE records.

The GRADE_HISTORY table contains thousands of rows and each EVENT can have multiple Grade changes.

In the EVENT_NARATIVE Table I need to find all records where the MESSAGE LIKE ‘CODE set to%’ There is only one MESSAGE that meets this criteria for each EVENT.

Once I have those records I need to find the first GRADE from the GRADE_HISTORY table that occurs after the Message_Time.

The result should look like

EventNum, Message, Message_Time, Grade, GradeChangeTime

My SQL looks like this but I know it doesn’t work

SELECT 
N.EventNum, N.SEQ, N.MESSAGE_TIME, N.MESSAGE,
G.CHANGE_DATE, G.GRADE
FROM 
    (SELECT EventNum, SEQ, MESSAGE_TIME, MESSAGE 
     FROM  EVENT_NARRATIVE 
     Where MESSAGE LIKE 'CODE set to%' ) N
Left JOIN   (SELECT Top 1 
             Event_Num, CHANGE_DATE, GRADE
             FROM  GRADE_HISTORY) G
ON G.Event_Num = N.Event_Num
AND G.CHANGE_DATE >= N.MESSAGE_TIME

SQL is not my day job so any help is appreciated to get the result I need.

SAMPLE DATA EVENT_NARATIVE

*EventNum         SEQ        MESSAGE_TIME                  MESSAGE*
000001-01012021   20770236   2021-01-01 00:03:36.0000000   CODE set to 6D02
000001-01022020   8339846    2020-02-01 00:06:14.0000000   CODE set to 17B01
000001-01022021   22038639   2021-02-01 00:04:44.0000000   CODE set to 17A02

SAMPLE DATA GRADE_HISTORY

*EventNum         CHANGE_DATE                       GRADE*
000001-01012021   2021-01-01 00:03:15.0000000       2
000001-01012021   2021-01-01 00:03:37.0000000       3
000001-01012021   2021-01-01 00:03:40.0000000       5
000001-01022020   2020-02-01 00:06:10.0000000       2
000001-01022020   2020-02-01 00:06:15.0000000       2
000001-01022020   2020-02-01 00:06:18.0000000       5
000001-01022020   2020-02-01 00:06:20.0000000       5
000001-01022021   2021-02-01 00:04:40.0000000       2
000001-01022021   2021-02-01 00:04:42.0000000       3
000001-01022021   2021-02-01 00:04:44.0000000       0
000001-01022021   2021-02-01 00:04:54.0000000       5

Expected Result

*EventNum         SEQ        CHANGE_DATE                        GRADE*
000001-01012021   20770236   2021-01-01 00:03:37.0000000        3  
000001-01022020   8339846    2020-02-01 00:06:15.0000000        2
000001-01022021   22038639   22021-02-01 00:04:44.0000000       0

Answer

Try this query

SELECT 
    N.EventNum, 
    N.SEQ, 
    N.MESSAGE_TIME, 
    N.MESSAGE,
    G.CHANGE_DATE, 
    G.GRADE
FROM 
(
    SELECT EventNum, SEQ, MESSAGE_TIME, MESSAGE 
    FROM  EVENT_NARRATIVE 
    WHERE MESSAGE LIKE 'CODE set to%' 
) AS N
OUTER APPLY (
    SELECT TOP 1 
        T.CHANGE_DATE,
        T.GRADE
    FROM GRADE_HISTORY AS T
    WHERE T.EventNum = N.EventNum AND T.CHANGE_DATE > N.MESSAGE_TIME
    ORDER BY T.CHANGE_DATE ASC
) AS G

Advertisement

Answer