当SQL表不是一对一时，如何计算准时交付

I have the following two tables:

我有以下两个表：

DueDates:

DueDates：

DECLARE @DueDates TABLE (
    uniqueln varchar(10),
    ship_dts smalldatetime,
    qty decimal(18,4))
INSERT INTO @DueDates
SELECT '51351621AS','1/1/2013',7
UNION ALL
SELECT '51351621AS','1/7/2013',7
UNION ALL
SELECT '51351621AS','1/14/2013',7
UNION ALL
SELECT '51351621AS','1/21/2013',7
UNION ALL
SELECT '51351621AS','1/28/2013',7
UNION ALL
SELECT 'V4351621AS','1/5/2013',10
UNION ALL
SELECT 'V4351621AS','1/10/2013',10
UNION ALL
SELECT 'V4351621AS','1/15/2013',10
UNION ALL
SELECT 'V4351621AS','1/20/2013',10
UNION ALL
SELECT 'V4351621AS','1/25/2013',10

PlDetail

DECLARE @PlDetail TABLE (
    uniqueln varchar(10),
    shipdate smalldatetime,
    shippedqty decimal(18,4))
INSERT INTO @PlDetail
SELECT '51351621AS','1/1/2013',10
UNION ALL
SELECT '51351621AS','1/7/2013',10
UNION ALL
SELECT '51351621AS','1/14/2013',10
UNION ALL
SELECT '51351621AS','1/21/2013',5
UNION ALL
SELECT 'V4351621AS','1/5/2013',13
UNION ALL
SELECT 'V4351621AS','1/15/2013',9
UNION ALL
SELECT 'V4351621AS','1/25/2013',12
UNION ALL
SELECT 'V4351621AS','1/30/2013',10
UNION ALL
SELECT 'V4351621AS','2/5/2013',6

The shipments in PlDetail can be one to one with the orders in the DueDates table, but often they are not.

PlDetail中的货件可以与DueDates表中的订单一对一，但通常不是。

I am trying to calculate an on-time delivery for each uniqueln schedule using a FIFO method (I cannot change how the data is stored in the tables). Basically, I want to apply the earliest shipments to the earliest deliveries.

我正在尝试使用FIFO方法计算每个uniqueln计划的准时交货（我不能改变数据在表中的存储方式）。基本上，我想将最早的货物应用到最早的货物中。

If a shipment qty exceeds the balance in a DueDates record, it should have the balance applied to the next scheduled delivery.

如果货件数量超过DueDates记录中的余额，则应将余额应用于下一个预定交货。

The end result should look something like this:

最终结果应如下所示：

uniqueln    ship_dts    qty shipdate    shippedqty  daysLate
51351621AS  1/1/2013    7   1/1/2013    7            0
51351621AS  1/7/2013    7   1/1/2013    3           -6
51351621AS  1/7/2013    7   1/7/2013    4            0
51351621AS  1/14/2013   7   1/7/2013    6           -7
51351621AS  1/14/2013   7   1/14/2013   1            0
51351621AS  1/21/2013   7   1/14/2013   7           -7
51351621AS  1/28/2013   7   1/14/2013   2           -14
51351621AS  1/28/2013   7   1/21/2013   5           -7
V4351621AS  1/5/2013    10  1/5/2013    10           0
V4351621AS  1/10/2013   10  1/5/2013    3           -5
V4351621AS  1/10/2013   10  1/15/2013   7            5
V4351621AS  1/15/2013   10  1/15/2013   2            0
V4351621AS  1/15/2013   10  1/25/2013   8           10
V4351621AS  1/20/2013   10  1/25/2013   4            5
V4351621AS  1/20/2013   10  1/30/2013   6           10
V4351621AS  1/25/2013   10  1/30/2013   4            5
V4351621AS  1/25/2013   10  2/5/2013    6           11

I know how to group the PlDetail shipments strictly by date so any shipment that falls on or before the next due date is grouped together, but it HAS to factor in the scheduled qty vs the shipped qty.

我知道如何严格按日期对PlDetail货件进行分组，以便将下一个截止日期或之前的任何货物组合在一起，但它必须考虑到预定的数量与运输的数量。

I don't want to create a cursor and cycle through the shipment records, but I can if this type of join won't work. However, I believe it is possible, but I am not sure how to group or join the tables.

我不想创建游标并循环浏览货件记录，但是如果这种类型的联接不起作用，我可以。但是，我认为这是可能的，但我不确定如何分组或加入表格。

It sounds like SQL Server 2012 has some new methods that will make this easier, but right now I am using SQL SERVER 2008 R2 and have to keep it that way for the near future.

听起来SQL Server 2012有一些新的方法可以使这更容易，但是现在我正在使用SQL SERVER 2008 R2并且必须在不久的将来保持这种方式。

What is the best way to approach this? Is a cursor really the only way?

解决这个问题的最佳方法是什么？光标真的是唯一的方法吗？

UPDATE: This is what I have added so far. The end result is a table showing the from and to qty and ship_dts for each uniqueln

更新：这是我到目前为止添加的内容。最终结果是一个表格，显示每个uniqueln的from和to qty以及ship_dts

WITH DSeq AS (
   SELECT TOP 100 PERCENT
      Seq = Row_Number() OVER (partition by uniqueln ORDER BY ship_dts),
      D.UNIQUELN,
      D.SHIP_DTS,
      SchBal = (SELECT TOP 100 PERCENT SUM(B.Qty) FROM @DueDates B WHERE b.SHIP_DTS<= D.SHIP_DTS AND b.UNIQUELN=d.UNIQUELN ORDER BY d.SHIP_DTS)
   FROM @DueDates D
   GROUP BY UNIQUELN,D.QTY,D.UNIQUELN,D.SHIP_DTS
   ORDER BY D.UNIQUELN, D.SHIP_DTS
)
--SELECT * FROM DSeq
, Slices AS (
   SELECT
      LN = D.UNIQUELN,
      FromQty = COALESCE(N.SchBal,0),
      ToQty = D.SchBal,
      D.SHIP_DTS
   FROM
      DSeq D
      LEFT OUTER JOIN DSeq N
         ON D.Seq -1 = N.Seq AND D.UNIQUELN=N.UNIQUELN
)
SELECT * FROM Slices

CURSOR APPROACH:

游标方法：

As has been stated, the best approach might be a cursor. Hopefully someone has a join method that will meet all the needs, but until then we are using a cursor.

如上所述，最好的方法可能是光标。希望有人有一个满足所有需求的连接方法，但在此之前我们正在使用游标。

In case someone is looking for a solution to this with a cursor approach, the code below is how we have done it. The user selects a date range and it produces the results. Please note that you HAVE to run the cursor for ALL records of the uniqueln even if they shipped prior to the selected date range, otherwise the FIFO application of shipments will be wrong.

如果有人用光标方法寻找解决方案，下面的代码就是我们如何做到的。用户选择日期范围并生成结果。请注意，即使它们在所选日期范围之前发货，您也必须为所有uniqueln记录运行光标，否则FIFO应用程序的运输将出错。

DECLARE @startdate smalldatetime, @endDate smalldatetime

DECLARE @OnTime TABLE (Uniqueln varchar(10),DueQty int,dueDate smalldatetime,shipDate smalldatetime,shipQty int DEFAULT 0,daysLate int,balQty int)


DECLARE @uniqln1 varchar(10),@toQty int, @dueDate smalldatetime,@bQty int
DECLARE @uniqln2 varchar(10),@shipQty int, @shipDate smalldatetime

DECLARE ot_cursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT uniqueln,qty,ship_dts,qty
    FROM @DueDates 
    ORDER BY uniqueln,ship_dts 
OPEN ot_cursor;
FETCH NEXT FROM ot_cursor INTO @uniqln1,@toQty,@dueDate,@bQty

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE s_cursor CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT Uniqueln,shippedqty,shipdate
        FROM @PlDetail p
        WHERE uniqueln = @uniqln1
        ORDER BY 3
    OPEN s_cursor ;
    FETCH NEXT FROM s_cursor INTO @uniqln2,@shipQty,@shipDate
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @OnTime(Uniqueln,DueQty,dueDate,shipDate,shipQty,daysLate,balQty)
        SELECT @uniqln1,@toQty,@dueDate,@shipDate,CASE WHEN @bQty>@shipQty THEN @shipQty ELSE @bQty END,DATEDIFF(d,@dueDate,@shipDate),CASE WHEN @bQty>@shipQty THEN @bQty-@shipQty ELSE 0 END
        SET @bQty=@bQty-@shipQty
        IF @bQty < 0 
        BEGIN
            SET @shipQty = -@bQty 
            FETCH NEXT FROM ot_cursor INTO @uniqln1,@toQty,@dueDate,@bQty
        END
        ELSE 
        IF @bQty =0
        BEGIN
            BREAK
        END
        ELSE
        BEGIN
            SET @shipQty = @bQty
            FETCH NEXT FROM s_cursor INTO @uniqln2,@shipQty,@shipDate
        END
    END
    CLOSE s_cursor
    DEALLOCATE s_cursor
    FETCH NEXT FROM ot_cursor INTO @uniqln1,@toQty,@dueDate,@bQty
END
CLOSE ot_cursor
DEALLOCATE ot_cursor

SELECT * FROM @OnTime 
    WHERE shipDate BETWEEN @startdate AND @endDate
    ORDER BY Uniqueln,dueDate

1 个解决方案

#1

Tried putting something together using CTEs and a pidgin cross join between the tables. (don't ask, it was ugly)

尝试使用CTE和表之间的pidgin交叉连接将一些东西放在一起。（不要问，这很难看）

Anywho, after a bit of reading, and knowing how large the tables are, I feel pretty safe answering this with... drumroll... use a cursor. It'll be ugly and slow, but the logic will make much more sense on paper. There's a lot to be said about maintainable code...

任何人，经过一些阅读，并知道桌子有多大，我觉得很安全地回答这个...鼓...使用光标。这将是丑陋和缓慢的，但逻辑将在纸上更有意义。关于可维护代码有很多话要说......

Update: Going on vacation. Here's what I was playing with.

更新：去度假。这就是我正在玩的东西。

DECLARE @DueDates TABLE (
    uniqueln varchar(10),
    ship_dts smalldatetime,
    qty decimal(18,4))
INSERT INTO @DueDates
SELECT '51351621AS','1/1/2013',7
UNION ALL
SELECT '51351621AS','1/7/2013',7
UNION ALL
SELECT '51351621AS','1/14/2013',7
UNION ALL
SELECT '51351621AS','1/21/2013',7
UNION ALL
SELECT '51351621AS','1/28/2013',7
UNION ALL
SELECT 'V4351621AS','1/5/2013',10
UNION ALL
SELECT 'V4351621AS','1/10/2013',10
UNION ALL
SELECT 'V4351621AS','1/15/2013',10
UNION ALL
SELECT 'V4351621AS','1/20/2013',10
UNION ALL
SELECT 'V4351621AS','1/25/2013',10


DECLARE @PlDetail TABLE (
    uniqueln varchar(10),
    shipdate smalldatetime,
    shippedqty decimal(18,4))
INSERT INTO @PlDetail
SELECT '51351621AS','1/1/2013',10
UNION ALL
SELECT '51351621AS','1/7/2013',10
UNION ALL
SELECT '51351621AS','1/14/2013',10
UNION ALL
SELECT '51351621AS','1/21/2013',5
UNION ALL
SELECT 'V4351621AS','1/5/2013',13
UNION ALL
SELECT 'V4351621AS','1/15/2013',9
UNION ALL
SELECT 'V4351621AS','1/25/2013',12
UNION ALL
SELECT 'V4351621AS','1/30/2013',10
UNION ALL
SELECT 'V4351621AS','2/5/2013',6



; WITH DueDates AS 
(
    SELECT b.*
    FROM @DueDates a
    JOIN @DueDates b
        ON a.uniqueln = b.uniqueln
        AND b.ship_dts >= a.ship_dts
)
, PlDetail AS
(
    SELECT b.*
    FROM @PlDetail a
    JOIN @PlDetail b
        ON a.uniqueln = b.uniqueln
        AND b.shipdate >= a.shipdate
)
SELECT a.uniqueln
    , SUM(a.qty) AS ordered_running_total
    , SUM(b.shippedqty) AS shipped_running_total
    , a.ship_dts
    , b.shipdate
    , SUM(b.shippedqty) - SUM(a.qty) AS leftover_running_total
FROM DueDates a
JOIN PlDetail b
    ON a.uniqueln = b.uniqueln
    AND a.ship_dts >= b.shipdate
GROUP BY a.uniqueln, a.ship_dts, b.shipdate
HAVING SUM(a.qty) <= SUM(b.shippedqty)
ORDER BY a.uniqueln, a.ship_dts, b.shipdate

#1