T-SQL根据最近的日期自动创建表 [英] T-SQL JOIN Table On Self Based on Closest Date

问题描述

感谢您提前阅读！

我想回答的问题是：零件真的需要付多少钱？我们通过将原始金属坯料加工成金属零件制造。最终零件出售给客户，废金属从出货到废料场。

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.

对于业务/ ERP配置原因，我们的废料供应商被列为客户，我们像其他客户一样运送他的零件。这些虚拟部件仅仅是我们所使用的每种金属合金，所以我们使用的每种合金都有一个假的废料部分。当我们填写我们的废料箱时，废料的出货量就是这样，所以没有定义的时间间隔。

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.

我已经尝试过其他解决方案，无法让他们工作。我正在哭泣叔叔，我根本无法让它工作....网络SQL界面我们的ERP使用声称它是T-SQL ...谢谢你阅读这么远！

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!

我希望输出的样子是：

 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>

推荐答案

这样的事情可能会成功：

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;

这篇关于T-SQL根据最近的日期自动创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！