I am trying to query using two tables: ORDERS and COSTS. Based on the PRODUCT_ID and ORDER_DATE in ORDERS I want to find the correct PRICE from COSTS using the PRODUCT_ID and PRICE_DATE. I had joined the two tables by PRODUCT_ID and wrote a criteria on PRICE_DATE of >=[ORDER_DATE] and <=[ORDER_DATE].
This seemed to work fine for about half of the orders. It worked on orders where the PRODUCT_ID gets a PRICE everyday in COSTS. However, there are some PRODUCT_ID that only get updated once a week, or once a month, or whenever someone deems a change is required.
So, PRODUCT_ID 25 may have entries like 01/01/2020 $24.50; 01/15/2020 $24.25; 02/01/2020 $24.99; 03/01/2020 $24.75. If the ORDER_DATE is today (3/5) then I'd want to grab a PRICE of $24.75; if it was 1/10 then I'd want $24.50; etc.
Any idea on how I could write the query to grab the correct PRICE for each order in my population? Let me know if you need any other information. Thank you.
This seemed to work fine for about half of the orders. It worked on orders where the PRODUCT_ID gets a PRICE everyday in COSTS. However, there are some PRODUCT_ID that only get updated once a week, or once a month, or whenever someone deems a change is required.
So, PRODUCT_ID 25 may have entries like 01/01/2020 $24.50; 01/15/2020 $24.25; 02/01/2020 $24.99; 03/01/2020 $24.75. If the ORDER_DATE is today (3/5) then I'd want to grab a PRICE of $24.75; if it was 1/10 then I'd want $24.50; etc.
Any idea on how I could write the query to grab the correct PRICE for each order in my population? Let me know if you need any other information. Thank you.