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

Question

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

Show us some sample table data and the expected result - all as formatted text, not images. — jarlh, Jul 20 '21 at 09:06
No problem. Remember that the easier you make it for us to assist, the better answers you'll get! — jarlh, Jul 20 '21 at 09:16
What is the best way to paste sample data. Sorry for my noobness — cramar, Jul 20 '21 at 09:23
@cramar just create a table for pasting sample data and show your output — GGG, Jul 20 '21 at 09:33
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jul 20 '21 at 11:25

score 0 · Accepted Answer · answered Jul 20 '21 at 10:25

0

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

answered Jul 20 '21 at 10:25

April Rain

81
4

I just tested on a small dataset and it works perfectly - Thankyou @April Rain so much. I have also leant something new - Outer Apply... – cramar Jul 20 '21 at 10:36

score 0 · Answer 2 · answered Jul 20 '21 at 10:35

you can use window function

SELECT EventNum,SEQ,CHANGE_DATE,GRADE
FROM
    (SELECT 
      N.EventNum,
      N.SEQ,
      G.CHANGE_DATE,
      G.GRADE,ROW_NUMBER() OVER(PARTITION BY N.EventNum ORDER BY (SELECT null)) AS NUM
    FROM EVENT_NARATIVE N 
    JOIN GRADE_HISTORY G ON N.EventNum = G.EventNum
    WHERE G.CHANGE_DATE >= N.MESSAGE_TIME AND N.MESSAGE LIKE 'CODE set to%') T
WHERE NUM = 1

demo in db<>fiddle

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

2 Answers2