sql - 检查数据是否遵循计划的顺序

Question

所以我很难尝试解决这个问题。

我目前有一个这样的 Material 生产计划(每一行是一个批处理):

SELECT
    Material, 
    Quantity,
    Range,
    OrderBy
FROM
    ProductionPlan
ORDER BY
    OrderBy

+----------+----------+-------+---------+
| Material | Quantity | Range | OrderBy |
+----------+----------+-------+---------+
| A        |      120 |     5 |       1 |
| B        |      120 |     5 |       2 |
| A        |      120 |     5 |       3 |
| C        |      120 |    10 |       4 |
| A        |      120 |     5 |       5 |
| A        |      120 |     5 |       6 |
+----------+----------+-------+---------+

而我们的实际生产数据是这样的:

SELECT
    Material, 
    Quantity,
    BatchNm 
FROM
    ProducedMaterials
ORDER BY
    BatchNm 
+----------+----------+---------+
| Material | Quantity | BatchNm |
+----------+----------+---------+
| A        |      120 |     101 |
| B        |      113 |     102 |
| C        |      111 |     103 |
| A        |      353 |     104 |
+----------+----------+---------+

我需要知道的是每个计划的 Material 是否通过检查几件事来实现，如果是它应该显示 GOOD，否则显示 BAD:

• 如果生产数量等于计划数量(+-Range)

• 按计划按相同订单生产。

当订单被打破时:它一直在寻找生产的 Material 。在示例中，他正在寻找 C，但正确的顺序是 A。它显示该行的 BAD，如果 Material 匹配，则查看下一行。在生产的 Material 匹配之前，它会一直显示计划数量的 BAD；

所以我应该以这样的结尾:

+----------+----------+-------+---------+--------+
| Material | Quantity | Range | OrderBy | Status |
+----------+----------+-------+---------+--------+
| A        |      120 |     5 |       1 |   GOOD | <-- Was produced first and quantity is within range
| B        |      120 |     5 |       2 |    BAD | <-- The Produced quantity(113) is not withing planned range
| A        |      120 |     5 |       4 |    BAD | <-- Bad because it didn't follow the plan (Suposed to be Material C)
| C        |      120 |    10 |       3 |   GOOD | <-- Good because it IS the next produced material AND it's quantity matched the planned quantity
| A        |      120 |     5 |       5 |   GOOD | <-- Good because it matches the next planned material AND the quantity is withing range(because the next row is the same material)
| A        |      120 |     5 |       6 |    BAD | <-- Bad because even thought the planned order is ok (Same material as the above row), the remaining quantity is above the quantity range (353 - 120(from the above row) = 233 "remaining" material)
+----------+----------+-------+---------+--------+

我很抱歉英语不好，但我很感激你的帮助。

P.S. 我现在不在我的开发机器上。完成后，我将发布我正在处理的功能；

Answer 1

以下解决方案有点冗长，但希望它能解决问题。我没有在所有可能的场景中测试这种方法，所以代码中出错的可能性仍然很大。查询将无法正确处理最初从未在计划中的 Material 的生产。例如，如果实际生产包括“D”但“D”不在计划中。

DECLARE @ProductionPlan TABLE 
(
     [Order] BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED  -- Use identity column to define order of plan
    ,Material CHAR(1) not null
    ,Quantity INT not null
    ,TolerenceRange INT not null
)
DECLARE @ProducedMaterials TABLE 
(
     [Order] BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED -- Use identity column to define order of actual production
    ,Material CHAR(1) not null 
    ,QuantityProduced INT not null
)

INSERT @ProductionPlan (Material, Quantity, TolerenceRange) 
VALUES    ('A',120,5)
    ,('B',120,5)
    ,('A',120,5)
    ,('C',120,10)
    ,('A',120,5)
    ,('A',120,5)
    ,('A',120,5);


INSERT @ProducedMaterials (Material,QuantityProduced)
VALUES     ('A',120)
    ,('B',113)
    ,('C',111)
    ,('A',353);


DECLARE @planEntriesCount INT = (SELECT COUNT([ORDER]) FROM @ProductionPlan);
DECLARE @productionEntriesCount INT = (SELECT COUNT([ORDER]) FROM @ProducedMaterials);
DECLARE @maximumDistance INT = ABS(@planEntriesCount - @productionEntriesCount);

WITH CTE_PlanAndActual
AS
(
    -- Join actual production with scheduled production on Material type only. For each possible 
    -- combination the query will calculate the “Distance” in order of execution between the actual 
    -- production step and the planned production steps. 

    SELECT   [Plan].[Order] AS [PlanedOrder]
            ,ISNULL([Actual].[Order], -1000) AS [ActualOrder]
            ,[Plan].[Material]
            ,ISNULL(ABS([Plan].[Order] - [Actual].[Order]), -10000000000) AS [Distance] -- Distance between actual production order and planned production order.  
            ,[Plan].[Quantity] AS [PlannedQuantity]
            ,ISNULL([Actual].[QuantityProduced], 0) AS [ActualQuantity]
            ,[Plan].[TolerenceRange]
    FROM    @ProductionPlan [Plan]
    LEFT OUTER JOIN @ProducedMaterials [Actual] ON [Actual].[Material] = [Plan].[Material]
)
,CTE_PlanAndActualBestMatch
AS
(
    --Next step we will use windowing function to determine the minimum distance between planned production step and actual production step.  
    --This will help us determine the best match with the information we have thus far.

    SELECT   [PlanedOrder]
            ,[ActualOrder]
            ,[Material]
            ,[Distance]
            ,MIN([Distance]) OVER (PARTITION BY [PlanedOrder]) AS [MinDistance]
            ,[PlannedQuantity]
            ,[ActualQuantity]
            ,[TolerenceRange]
    FROM    CTE_PlanAndActual
)
,CTE_PlanAndActualOrderValidated
AS
(
    -- Next eliminate records which does not meet the minimum distance criteria for each planned production step.
    -- Now that we have only the records that matches the minimum distance criteria we need to determine if any 
    -- of the actual production execution steps was out of order. We will use the LEAD windowing function to determine this.
    SELECT   [PlanedOrder]
            ,(
                -- If one or more step is out of order, then it means the production plan was not followed. Simply set the Actual order value for the record to null.
                CASE 
                    WHEN [ActualOrder] >  LEAD([ActualOrder], 1, [ActualOrder]) OVER (ORDER BY [PlanedOrder]) THEN NULL
                    WHEN ( ([PlanedOrder] = 1) AND ([ActualOrder] <> 1) ) THEN NULL
                    WHEN ( [Distance] > @maximumDistance) THEN NULL
                    ELSE [ActualOrder]
                END
             ) [ActualOrder]
            ,[Material]
            ,[Distance]
            ,[PlannedQuantity]
            ,[ActualQuantity]
            ,[TolerenceRange]
    FROM    CTE_PlanAndActualBestMatch
    WHERE   [MinDistance] = [Distance]  -- Eliminate records that is not the minimum distance between plan and actual.
)
,CTE_PlanAndActualWithRepeats
AS
(
    -- Next determine repeated planned orders this will be needed to correctly determine if the 
    -- production quantiles were within in planned tolerance range.
    -- Also calculate the Cumulative Planed Quantity for planned entries that repeat, this will 
    -- be needed to determine if repeated production entries are within tolerance range.

    SELECT   [PlanedOrder]
            ,[ActualOrder]
            ,[Material]
            ,[Distance]
            ,[PlannedQuantity]
            ,IIF([ActualOrder] IS NULL,  NULL, [ActualQuantity]) AS [ActualQuantity]
            ,[TolerenceRange]
            ,IIF([ActualOrder] IS NULL,  0, 1) AS PlanFollowed
            ,COUNT([PlanedOrder]) OVER (PARTITION BY [ActualOrder]) AS RepeatCount
            ,ROW_NUMBER() OVER (PARTITION BY [ActualOrder] ORDER BY [PlanedOrder]) AS RepeatIndex
            ,SUM([PlannedQuantity]) OVER (PARTITION BY [ActualOrder] ORDER BY [PlanedOrder]) AS [CumulativePlanedQuantity]
    FROM    CTE_PlanAndActualOrderValidated

)
,CTE_PlanAndEffectiveProduction
AS
(

    -- Calculate the effective production. In the event that production plan entry repeats the 
    -- effective production will use, the final effective production value will be calculated 
    -- from total actual production and cumulative planned production.
    SELECT   [PlanedOrder]
            ,[ActualOrder]
            ,[Material]
            ,[Distance]
            ,[PlannedQuantity]
            ,[ActualQuantity]
            ,[TolerenceRange]
            ,[PlanFollowed]
            ,[RepeatCount]
            ,[RepeatIndex]
            ,[CumulativePlanedQuantity]
            ,(
                CASE
                    WHEN ([RepeatIndex] < [RepeatCount]) AND ([CumulativePlanedQuantity] < [ActualQuantity]) THEN [PlannedQuantity]
                    WHEN ([RepeatIndex] > 1) AND ([RepeatIndex] = [RepeatCount]) THEN [ActualQuantity] - ([CumulativePlanedQuantity] - [PlannedQuantity])
                    ELSE [ActualQuantity]
                END
             ) AS EffectiveQuantity
    FROM    CTE_PlanAndActualWithRepeats
)
-- Finally determine status
SELECT   [PlanedOrder]
        ,[ActualOrder]
        ,[Material]
        ,[Distance]
        ,[PlannedQuantity]
        ,[ActualQuantity]
        ,[TolerenceRange]
        ,[PlanFollowed]
        ,[RepeatCount]
        ,[RepeatIndex]
        ,[CumulativePlanedQuantity]
        ,[EffectiveQuantity]
        ,(
            CASE 
                WHEN ([PlanFollowed] = 1) AND (ABS([EffectiveQuantity] - [PlannedQuantity]) <= [TolerenceRange]) THEN 'Good'
                WHEN ([PlanFollowed] = 1) AND (ABS([EffectiveQuantity] - [PlannedQuantity]) > [TolerenceRange]) THEN 'Bad - Out of Range' 
                WHEN ([PlanFollowed] = 0) THEN 'Bad - Plan Not Followed'
                ELSE 'Bad'
            END 
        ) [Status]
FROM    CTE_PlanAndEffectiveProduction
ORDER BY [PlanedOrder]