Compare dates from two tables dynamically

[Anonymous]

Hi,

i have events, that point to an order. the relation is not 1:1 but n:m.
i want to assign the order value to the event, that is closest in the past.

i hope this example tables make it clear and arent too confusing. Thanks a lot!

Orders:

orderId	orderEventKey	value	orderDate
1	2	45	2022-01-15
2	2	74	2021-11-25
3	3	150	2022-01-18
4	4	70	2022-01-20

Events:

eventId	orderEventKey	eventDate
1	2	2022-01-14
2	3	2022-01-14
3	3	2022-01-19
4	4	2022-01-19
5	4	2022-01-18

Result-Table:

eventId	value
1	45
2	150
3	0
4	70
5	0

edit: changed column-names from "date" to "orderDate" and "eventDate".

[chris_djih]

If you want to do it in script you may try:

Last_Event_Value:
Load 
   orderEventKey,
   // sorts asc "-orderdate" => desc orderdate
   firstsortedvalue( value, -orderdate) as lastValue
resident orders
group by orderEventKey
;