How to get the maximum record by a key where table 2 date field is less than or equal to date in master table

[#1]

I have two basic tables, one that retains events that can occur, and the other retains patients vital measurements.

The events table can have multiple events for the same patients, and the vitals table will hae multiple vital sign readings. (inline below)

For each event I want to find the last vital measurement that was recorded for the patient, that was taken at/or before the event time. For the sample data below I would want to the first record for patid 1 should end up finding the 3:02 AM value since the others occurred after 8:00 AM event time. The second event should find the 11:43 PM value. For patient 2 it should find the 7:41 value.

Any help would be appreciated that could point me in the right direction. I have used the INTERVAL MATCH for other things, but I don't want duplicate matches I just want the maximum one, that is <= eventdate

Events:
LOAD * INLINE [
    PATID, EVENTDATE
    1, 12/13/2011 8:00 AM
    1, 12/13/2011 12:00 PM
    2, 12/12/2011 7:43 PM
];

Vitals:
LOAD * INLINE [
    PATID, VITALDATE, VALUE
    1, 12/13/2011 3:02 AM, 6
    1, 12/13/2011 8:17 AM, 5
    1, 12/13/2011 11:42 PM, 14
    1, 12/13/2011 11:43 PM, 3
    2, 12/12/2011 7:41 PM, 12
    2, 12/12/2011 7:35 PM, 10
];

[Gysbert_Wassenaar]

See attached qvw. Is that what you're looking for?

The second event should find the 11:43 PM value

12.00 PM is noon, isn't it? And 11:43 PM is rather late at night. So why should 11:43 be found for 12.00 PM? Is there a typo somewhere or am I missing something? I'm used to a 24h clock, so AM/PM doesn't come naturally to me.

[#3]

You are correct, say the event date was 11:59 PM instead of 12:00 PM, since I didn't even think about the roll over was just looking for simple matches.

Solution works great in the chart.

I should have been more specific I'm trying to do this in the LOAD script, as this will be done for lots of different vitals.

[Anonymous · Accepted solution]

See if this helps:

Events:

LOAD
PATID,
timestamp#(EVENTDATE, 'MM/DD/YYYY h:mm TT') as EVENTDATE
INLINE [
    PATID, EVENTDATE
    1, 12/13/2011 8:00 AM
1, 12/13/2011 12:00 PM
    2, 12/12/2011 7:43 PM
];

Vitals:
LOAD
PATID,
timestamp#(VITALDATE, 'MM/DD/YYYY h:mm TT') as VITALDATE,
VALUE
INLINE [
PATID, VITALDATE, VALUE
1, 12/13/2011 3:02 AM, 6
    1, 12/13/2011 8:17 AM, 5
    1, 12/13/2011 11:42 PM, 14
    1, 12/13/2011 11:43 PM, 3
    2, 12/12/2011 7:41 PM, 12
    2, 12/12/2011 7:35 PM, 10
];

tmp:
NOCONCATENATE
LOAD
PATID,
EVENTDATE
RESIDENT Events;
LEFT JOIN (tmp) LOAD
PATID,
VITALDATE
RESIDENT Vitals;

LEFT JOIN (Events) LOAD
PATID,
EVENTDATE,
timestamp(max(if(VITALDATE<=EVENTDATE, VITALDATE))) as MaxVitalDate
RESIDENT tmp
GROUP BY PATID, EVENTDATE;

DROP TABLE tmp;

[#5]

Thank you both SO SO SO SO much.

I'm cooking with gas now.