Joins with distinguish

[arsenal1983]

Hello,

I'm looking for some help with the issue I've got. I have two tables ORDERS and RECEIPTS. I need to find the first RECEIPT ID that match the order (matched meens the same SHOP+VALUE and RECEIPT DATA >= ORDER_DATE). The problem I've got is that I only can join on SHOP+VALUE and one RECEIPT should be used only once (to the first matched ORDER). Example:

ORDERS:

ORDER_SHOP	ORDER_VALUE	ORDER_DATE
D001	10	01.10.2018
D001	10	15.10.2018
D001	10	15.10.2018
D001	10	01.11.2018
D001	10	24.12.2018
D001	15	11.10.2018
D002	20	11.10.2018
D003	10	15.10.2018

RECEIPTS:

REC_NO	REC_DATE	REC_SHOP	REC_VALUE
P_001	02.09.2018	D001	10
P_002	30.10.2018	D001	10
P_003	30.10.2018	D001	10
P_004	25.11.2018	D001	10
P_005	30.10.2018	D001	15

Result I Expect:

ORDER_SHOP	ORDER_VALUE	ORDER_DATE	REC_NO	REC_DATE
D001	10	01.10.2018	P_002	30.10.2018
D001	10	15.10.2018	P_003	30.10.2018
D001	10	15.10.2018	P_004	25.11.2018
D001	10	01.11.2018
D001	10	24.12.2018
D001	15	11.10.2018	P_005	30.10.2018
D002	20	11.10.2018
D003	10	15.10.2018

[chrismarlow · Accepted solution]

Hi,

I started this thinking although tricky there would be a neat solution in the end … the attached does work, but is a bit longer than I thought it would be;

I am not sure if the IntervalMatch (61-67) simplifies or not - I thought that might be able to avoid using the loop (92-116), by using it but ended up looping through the data anyway.

Post back if you cannot open the file (I have personal edition) - I can post up the code.

Cheers,

Chris

[arsenal1983]

Hi, 

thanks a lot! 

great solution!