SQL Server : returning a cost based on a date range between 2 tables

Question

I have 2 tables, one for transactions of inventory and one for standard cost history. I am trying to get the standard from the cost table by using the dates to determine what standard to select between the 2 tables.

So as an example:

ProductID   Trans.Date
-----------------------
P001        12/26/2017
P001        10/8/2018
P001        12/17/2018
P001        1/2/2019

ItemID  EffectiveDate   Standard
---------------------------------
P001    12/23/2017         50
P001    12/31/2018         52
P002    12/23/2017        100
P002    12/31/2018        103

My subquery is:

(SELECT 
     SUM(c305.t_tvat_1)
 FROM 
     tticpr305100 c305      
 WHERE 
     c305.t_item = w112.t_item 
     AND c305.t_indt <= w112.t_trdt) AS 'STD.COST'

Where tvat_1 is the total value of materials, labour and other costs, and t_item = PRODUCT ID

What I want to see:

ProductID   Trans.Date  STD_Cost
---------------------------------
P001        12/26/2017     50
P001        10/8/2018      50
P001        12/17/2018     50
P001        1/2/2019       52

What I am seeing:

ProductID   Trans.Date  STD_Cost
---------------------------------
P001        12/26/2017     50
P001        10/8/2018      50
P001        12/17/2018     50
P001        1/2/2019      102

Answer 1

One option is a CROSS APPLY

Not sure if the column name is [Trans.Date] or [Date] in the trans table.

In the example below, T1 is your first table displayed, and T2 is the second table displayed.

Example dbFiddle

Select A.*
      ,STD_Cost = B.Standard
 From  T1 A
 Cross Apply (
              Select Top 1 *
               From  T2
               Where TemID=A.ProductID and EffectiveDate<=[Trans.Date]
               Order By EffectiveDate Desc
             ) B

Returns

ProductID   Trans.Date  STD_Cost
P001        2017-12-26  50.00
P001        2018-10-08  50.00
P001        2018-12-17  50.00
P001        2019-01-02  52.00

Note: Use OUTER APPLY if you want to see NULL values