如何在SQL Server 2008查询中显示订单履行 [英] How to show order fulfilment in a SQL Server 2008 query

问题描述

我正在尝试在 SQL Server 2008 数据库上运行销售​​订单表并获得零件的未结需求，按到期日期订购然后查看购买的方法订单表并按PO履行销售订单，还应按到期日订购PO供应.同时，我需要显示哪些PO正在履行销售订单.

I am trying to think of a way on a SQL Server 2008 database to run through a sales order table and get open demand for a part, order it by due date, then look at a purchase order table and fulfill the sales orders by PO, ordering the PO supply by due date as well. At the same time, I need to show what PO(s) are fulfilling the sales order.

例如:

SO 表

SO#     DueDate     Part Number  Required QTY
---------------------------------------------
100     9/3/16      1012          2
101     9/12/16     1012          1
107     10/11/16    1012          4
103     10/17/16    1012          7

PO 表:

PO#     DueDate     Part Number  Ordered QTY
--------------------------------------------
331     9/1/16      1012          1
362     9/2/16      1012          1
359     9/24/16     1012          5
371     10/1/16     1012          3
380     10/10/16    1012          10

有了这些数据，我希望看到以下结果:

With this data, I would like to see this result:

SO#  DueDate     Part Number  Required QTY  PO number  QTY Used  QTY Remain
 --------------------------------------------------------------------------
100  9/3/16      1012          2             331         1         0 
100  9/3/16      1012          1             362         1         0
101  9/12/16     1012          1             359         1         4
107  10/11/16    1012          4             359         4         0
103  10/17/16    1012          7             371         3         0
103  10/17/16    1012          7             380         4         6

我之前已经完成了该销售订单的履行过程，但没有达到分解执行订单的PO的目的，只是达到了所有未清供应量的总和，然后逐一减去每个供应量的程度.销售订单以保持剩余的供应平衡.

I have done this sales order fulfillment process before, but not to the point of breaking down what PO(s) are fulfilling the order, only to the point of summing all open supply, then running through and subtracting the supply from each sales order to get a running balance of supply left.

在此先感谢您的帮助.

推荐答案

我找到了一个奇怪的解决方案，希望对您有所帮助.也许以后我可以对其进行优化，但是现在我将其按原样发布:

I found a bit weird solution, hope it helps you. Maybe later I could optimize it, but now I post it as is:

;WITH cte AS (
    SELECT 1 as l
    UNION ALL
    SELECT l+1
    FROM cte
    WHERE l <= 1000000
), SO_cte AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY DueDate ASC) as rn
    FROM SO s
    CROSS JOIN cte c
    WHERE c.l <= s.[Required QTY]
), PO_cte AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY DueDate ASC) as rn
    FROM PO p
    CROSS JOIN cte c
    WHERE c.l <= p.[Ordered QTY]
), almost_done AS (
    SELECT DISTINCT     
            s.SO#,
            s.DueDate,
            s.[Part Number],
            p.PO#,
            s.[Required QTY],
            p.[Ordered QTY]
    FROM SO_cte s
    LEFT JOIN PO_cte p
        ON p.rn = s.rn
), final AS (
    SELECT  *,
            ROW_NUMBER() OVER (ORDER BY DueDate) AS RN
    FROM almost_done
)

SELECT  f.SO#,
        f.DueDate,
        f.[Part Number],
        f.[Required QTY],
        f.PO#,
        CASE WHEN f.[Ordered QTY]>f.[Required QTY] 
                THEN ISNULL(ABS(f1.[Required QTY]-f1.[Ordered QTY]),f.[Required QTY]) 
                ELSE f.[Ordered QTY] END 
                        as [QTY Used],
        f.[Ordered QTY] - 
        CASE WHEN f1.PO# = f.PO# 
                THEN f1.[Ordered QTY]
                ELSE
                    CASE WHEN f.[Ordered QTY]>f.[Required QTY] 
                            THEN ISNULL(ABS(f1.[Required QTY]-f1.[Ordered QTY]),f.[Required QTY]) 
                            ELSE f.[Ordered QTY] END
         END as [QTY Remain]
FROM final f
LEFT JOIN final f1
    ON f.RN = f1.RN+ 1
        AND (f.SO# = f1.SO# OR f.PO# = f1.PO#)
OPTION(MAXRECURSION 0)

您提供的数据的输出:

SO# DueDate     Part Number Required QTY    PO# QTY Used    QTY Remain
100 2016-09-03  1012        2               331 1           0
100 2016-09-03  1012        2               362 1           0
101 2016-09-12  1012        1               359 1           4
107 2016-10-11  1012        4               359 4           0
103 2016-10-17  1012        7               371 3           0
103 2016-10-17  1012        7               380 4           6

这篇关于如何在SQL Server 2008查询中显示订单履行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！