Bring value from another table that is between two dates

(0) Share

Report

Posted on by LameImpala

I am working on a project where I have two tables

Table1 is transaction data and contains a specific date of transaction. i.e.

Customer	Item	Date
Walmart	Funyuns	1/13/2023

Table2 is Agreement data which contains a valid from and Valid To dates and a price.

Customer	Item	valid from	valid to	price
Walmart	Funyuns	1/1/2023	12/31/2023	$5.00

I would like to merge Table 1 and Table 2 on multiple dimensions then bring back the price from table 2 based on the transaction date in table 1, which falls between valid to and valid from in table 2.

I understand there is a way to merge the tables, then without expanding, bring back only the field you want based on the criteria (date from table 1 falls between valid from and valid to in table 2, bring back price).

Hope this makes sense.

Thanks for your help