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
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
Returns
Note: Use
OUTER APPLY
if you want to see NULL values