This may appear to be a repeat question but it is not because the other solutions in the forum don't work in this situation.
This is a query to our ERP database that is trying to get the final cost of goods sold total for parts. Basically the ERP makes it easy to get all the direct costs out but doesn't calculate scrap costs.
Where I'm stuck is in the sub query in the FROM section marked:
>>>>>>HELP NEEDED STARTING HERE
The sub query, as it is now written, pulls out all the shipments to our scrap vendor and gets a monthly average rate per pound, then joins to the other tables based on alloy type, month and year.
My Finance Department has told me the average is not a good solution, since some metal prices fluctuate too much or they don't sell the scrap metal in the same month the parts shipped so this won't work.
I need to get the rate we are paid for scrap metal from the closest date before the part was shipped from our facility.
I've found other examples on Stack Overflow that show ways to do this but the main tables and the sub query tables overlap so the other solutions I've seen have failed. I have commented in the code below to show and explain this.
I'm totally open to the idea I've approached this wrong. How can I make this work?
DECLARE @Date_From AS DATETIME;
DECLARE @Date_To AS DATETIME;
SET @Date_From = '2016-10-01 00:00:00.000';
SET @Date_To = GETDATE() ;
-- Start Main query
SELECT TOP 10
CCustomer.Customer_Type AS 'Industry'
,SShipper.Ship_Date AS 'Ship_Date'
,SSContainer.Serial_No AS 'Serial_No'
,PPart.Grade AS 'Alloy'
,tbl_ScrapValue.Scrap_Value_per_lb AS 'Scap_Value_per_lb'
FROM
Sales_v_Shipper_Line AS SSLine
JOIN Sales_v_Shipper AS SShipper
ON SShipper.Shipper_Key = SSLine.Shipper_Key
JOIN Part_v_Part AS PPart
ON SSLine.Part_Key = PPart.Part_Key
JOIN Common_v_Customer AS CCustomer
ON SShipper.Customer_No = CCustomer.Customer_No
-- >>>>>>HELP NEEDED STARTING HERE
-- Below is the sub query that pulls the scrap sales value per pound.
-- The key point is that both shipments to our customers of real parts,
-- and the 'shipments' of scrap metal sales come from the same tables,
-- mainly Part_v_Part and Sales_v_Shipper, because of that the other
--solutions for the 'join by closest date' in the forums don't work.
LEFT OUTER JOIN (SELECT
MONTH(SShipper.Ship_Date) AS 'Scrap_Ship_Month'
,YEAR(SShipper.SHip_Date) AS 'Scrap_Ship_Year'
,PPart.Grade AS 'Alloy'
,AVG(AARIDist.Unit_Price) AS 'Scrap_Value_per_lb'
FROM
Sales_v_Shipper AS SShipper
JOIN Sales_v_Shipper_Line AS SS_Line
ON SShipper.Shipper_Key = SS_Line.Shipper_Key
JOIN Part_v_Part AS PPart
ON SS_Line.Part_Key = PPart.Part_Key
JOIN Common_v_Customer AS CCustomer
ON SShipper.Customer_No = CCustomer.Customer_No
WHERE CCustomer.Customer_Code = 'Scrap_Vendor'
AND SSHipper.Ship_Date <= @Date_To
GROUP BY
MONTH(SShipper.Ship_Date)
,YEAR(SShipper.SHip_Date)
,PPart.Grade
) AS tbl_ScrapValue
ON PPart.Grade = tbl_ScrapValue.Alloy
AND
YEAR(SShipper.Ship_Date) = YEAR(tbl_ScrapValue.Scrap_Ship_Year)
AND
MONTH(SShipper.Ship_Date) =(tbl_ScrapValue.Scrap_Ship_Month)
--- >>>>HELP NEEDED ENDS HERE
WHERE
AND SShipper.Ship_Date >= @Date_From
AND SSHipper.Ship_Date <= @Date_To
GROUP BY
SShipper.Shipper_No
,SShipper.Ship_Date
,CCustomer.Customer_Type
,SSContainer.Quantity
,PPart.Grade
Here's a sample output from the query above, as you can see the 'Scrap_Value_per_lb' is failing:
[![Sample_Output][1]][1]
Industry Ship_Date Serial_No Alloy Scap_Value_per_lb
Material Processing 17-Oct-16 4:47:00 PM S472091 C182 NULL
Material Processing 17-Oct-16 4:47:00 PM S472210 C182 NULL
Material Processing 17-Oct-16 4:47:00 PM S472211 C182 NULL
Electronics 17-Oct-16 4:27:00 PM S436738 C180 NULL
Electronics 17-Oct-16 4:27:00 PM S463290 C180 NULL
Electronics 17-Oct-16 4:27:00 PM S463315 C180 NULL
Electronics 17-Oct-16 4:27:00 PM S463327 C180 NULL
Electronics 17-Oct-16 4:27:00 PM S463333 C180 NULL
Electronics 17-Oct-16 4:27:00 PM S463345 C180 NULL
Electronics 17-Oct-16 4:27:00 PM S463354 C180 NULL
This was edited a second time @7am 10/19/2016 to simplify code further, added comments in code to clarify based on feedback from others.
I spent a couple of hours thinking about this question and boiled the sample code down even further and re-posted the question here.
I think it makes more sense and I thank all of you who responded and tried to help answer what I wrote. It helped but I still could not get it to work.