I have two tables T_VEN and T_COM. In the first one I have the sales date of several products, in the second one I have the cost of these products per day. I want to join the tables to inform the cost of the product on the day of sale, but when there is no cost on that day, I want it to find the cost of the immediately previous day.

e.g.: (the dates are in DD/MM/YYYY format)

T_VEN
PROD|DATE
1 |01/02/2024
1 |02/02/2024
2 |02/02/2024
1 |03/02/2024

T_COM
PROD|DATE |COST
1 |01/02/2024|2.50
1 |02/02/2024|3.50
2 |01/02/2024|4.50

PROD|DATE |COST
1 |01/02/2024|2.50
1 |02/02/2024|3.50
2 |02/02/2024|4.50
1 |03/02/2024|3.50

I’ve tried doing joins in various ways, subqueries, extracting Max value with group by, join with case when clause, etc., but I’m having difficulties. I can easily connect in Excel with the XLOOKUP function with the Match_mode = -1 (exact match or next smaller. If an exact match is not found, the next smaller value is returned).

Can someone help me?

I’ve tried doing joins in various ways, subqueries, extracting Max value with group by, join with case when clause, etc., but I’m having difficulties. I can easily connect in Excel with the XLOOKUP function with the Match_mode = -1 (exact match or next smaller. If an exact match is not found, the next smaller value is returned).