I have 2 tables. I am trying to extract information from table 2 using the date value from table 1 and finding the most recent corresponding date on table 2 (that's less than the date on table 1). I'm not sure how to add tables on this forum, so please advice me, but I will type a rough outline of the table here.
Lets assume we're talking about applying discounts to a gym membership.
Table 1)
Customer State Date Joined
Adam CA 1/1/2017
Carllos TX 1/1/2018
Adam CA 1/1/2019
Table 2 Discounts
State Date Effective Discount
CA 1/1/2015 5%
CA 1/1/2018 10%
TX 1/1/2014 2%
TX 1/1/2017 3%
TX 1/1/2019 4%
The final table should look like this:
Customer State Date Joined Effective Discount
Adam CA 1/1/2017 5%
Carllos TX 1/1/2018 3%
Adam CA 1/1/2019 10%