CODEWITHSUNDEEP
sqlt-sqldatetimetimeinner-join
Python_Learner
Python_Learner

Reputation: 1637

T-SQL JOIN Table On Self Based on Closest Date

Thank you in advance for reading!

The question I'm trying to answer is: "How much do parts really cost to make?" We manufacture by machining raw metal billets down to metal parts. Final parts are sold to a customer and scrap metal from the process is sold to the scrap yard.

For business/ERP configuration reasons our scrap vendor is listed as a customer and we ship him 'parts' like our other customers. These dummy parts are simply for each of the metal alloys we work with, so there is one dummy scrap part for each alloy we use. The scrap shipments are made whenever we fill our scrap bins so there's no defined time interval.

I'm trying to connect the ship date of a real part to a real customer to the closest scrap ship date of the same alloy. Then I can grab the scrap value per pound we were paid and include it in our revenue for the parts we make. If I can ask for the world it would be helpful to know how to grab the scrap shipment immediately before or immediately after the shipment of a real part - I'm sure management will change their minds several times debating if they want to use the 'before' or 'after' number.

I've tried other solutions and can't get them to work. I'm crying uncle, I simply can't get it to work....the web SQL interface our ERP uses claims it's T-SQL... thank you for reading this far!

What I'd like the output to look like is:

 Customer  Part     Price   Alloy  Weight_Lost  Scrap_Value   Ship_Date  
 ABC       Widget1  99.99   C182    63            2.45         10-01-2016

Here's the simplest I can boil the tables down to:

   SELECT
    tbl_Regular_Sales.Customer
    tbl_Regular_Sales.Part
    tbl_Regular_Sales.Price
    tbl_Regular_Sales.Alloy
    tbl_Regular_Sales.Weight_Lost
    tbl_Scrap_Sales.Price AS 'Scrap_Value'
    tbl_Regular_Sales.Ship_Date
   FROM
    (SELECT P.Part
           ,P.Alloy
           ,P.Price
           ,S.Ship_Date
           ,S.Customer
    FROM    Part AS P
    JOIN    S AS S
    ON      S.Part_Key = P.Part_Key
    WHERE   Shipper.Customer = 'Scrap_Yard'
    ) AS tbl_Scrap_Sales
    JOIN
       (SELECT  P.Part
               ,P.Weight_Lost
               ,P.Alloy
               ,P.Price
               ,S.Ship_Date
               ,S.Customer
       FROM    Part AS P
       JOIN    S AS S
       ON      S.Part_Key = P.Part_Key
       WHERE   Shipper.Customer <> 'Scrap_Yard' ) AS tbl_Regular_Sales
    ON
    tbl_Regular_Sales.Alloy = tbl_Scrap_Sales.Alloy
    AND   <Some kind of date JOIN to get the closest scrap shipment value>

Upvotes: 1

Views: 617

Answers (1)

Lucero
Lucero

Reputation: 60190

Something like this may do the trick:

WITH cteScrapSales AS (
    SELECT 
        P.Alloy 
        ,P.Price
        ,S.Ship_Date
    FROM Part AS P
    JOIN Shipper AS S ON S.Part_Key = P.Part_Key
    WHERE S.Customer = 'Scrap_Yard'
), cteRegularSales AS (
    SELECT 
        P.Part_Key
        ,P.Part
        ,P.Weight_Lost
        ,P.Alloy
        ,P.Price
        ,S.Ship_Date
        ,S.Customer
    FROM Part AS P
    JOIN Shipper AS S ON S.Part_Key = P.Part_Key
    WHERE S.Customer <> 'Scrap_Yard'
)
SELECT
        C.Customer
        ,C.Part
        ,C.Price
        ,C.Alloy
        ,C.Weight_Lost
        ,C.Scrap_Value
        ,C.Ship_Date
    FROM (
        SELECT R.*, S.Price AS Scrap_Value, ROW_NUMBER() OVER (PARTITION BY R.Part_Key ORDER BY DATEDIFF(SECOND, R.Ship_Date, S.Ship_Date)) ix
        FROM cteRegularSales R 
        JOIN cteScrapSales S ON S.Allow = R.Allow AND S.Ship_Date > R.Ship_Date
    ) AS C
    WHERE C.ix = 1;

Upvotes: 1

Related Questions