Solved Linking Table With Multiple Rows

[woodsy74]

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.

 

[theDBguy]

Hi. It might be easier if you could post a demo version of your db with test data. Just a thought...

 

[ebs17]

SELECT
   O.PRODUCT_ID,
   O.ORDER_DATE,
   (
      SELECT TOP 1
         C.Price
      FROM
         COSTS AS C
      WHERE
         C.PRODUCT_ID = O.PRODUCT_ID
            AND
         C.PRICE_DATE <= O.ORDER_DATE
      ORDER BY
         C.PRICE_DATE DESC
   ) AS PRICE
FROM
   ORDERS AS O

 

[woodsy74]

Attached is a Test Version of what I am doing. This doesn't match the examples I posted originally but the concept is the same. In Query1 I am just trying to pull in Orders #1 and #7. Order #1 returns because it's a PRODUCT_ID that is priced daily. Order #7 is not returned because the PRODUCT_ID is priced every week or so.
Hope this helps. Let me know if you need anything else. Thanks.

 

[woodsy74]

EBS17, what you have listed seems to be working for me. I will try incorporating it with the real data to make sure it works. Thanks for all your help!