SQL - Join on changing dates

Question

I've been scratching my head on this for a week now.

Consider two tables - one tallying inventory:

+------------+--------------+----------+-------------------+
| product_id | product_name |   date   | on_hand_inventory |
+------------+--------------+----------+-------------------+
|          1 | Product A    | 6/1/2019 |                37 |
|          1 | Product A    | 6/2/2019 |                36 |
|          1 | Product A    | 6/3/2019 |                35 |
|          1 | Product A    | 6/4/2019 |                40 |
|          1 | Product A    | 6/5/2019 |                42 |
+------------+--------------+----------+-------------------+

... and another tracking costs:

+------------+----------------+------------+------------+------------+
| product_id | cost_component | cost_value | start_date |  end_date  |
+------------+----------------+------------+------------+------------+
|          1 | FOB            |         15 | 1/1/2019   | 6/1/2019   |
|          1 | FOB            |       15.5 | 6/2/2019   | 6/3/2019   |
|          1 | FOB            |         16 | 6/4/2019   | 12/31/9999 |
+------------+----------------+------------+------------+------------+

The layout of the cost table is what's driving me nuts. I need to join these tables to keep a running valuation of on-hand inventory, and I can't think of a method in SQL that would let me select the appropriate row in the cost table. A join on produt_id doesn't work because it would return all cost components for that item whether or not they apply to that date. I feel like should be involving a CASE statement, but I'm not sure what that would look like. This is in MSSQL 2016, for what its worth.

Answer 1

If you want the most recent cost, you can use join:

select t.*, c.*
from inventory i join
     costs c
     on c.product_id = i.product_id and
        i.date between c.start_date and c.end_date;