Felipe Deguchi Felipe Deguchi - 4 months ago 8
SQL Question

Check if data follows planned order

So I'm having a hard time trying solve this.

I currently have a Material production plan like this (each row is a batch):

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 |
+----------+----------+-------+---------+


And our actual production data looks like this:

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


What I need to know is if each planned Material was achieved by checking a couple of things, if it is it should show GOOD, otherwise show BAD:


  • If the produced quantity is equal the planned quantity (+-Range)

  • Was produced the same order as planned.



When the order is broken: It keeps looking for the produced material.
In the example he was looking for C, but the correct order was A. It shows a BAD for that row and looks at the next row if the material matches. Until the produced material matches it would keep showing BAD for the planned quantities;

So I should end with something like this:

+----------+----------+-------+---------+--------+
| 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)
+----------+----------+-------+---------+--------+


I'm sorry about the bad english, but I apreciate your help.

P.S. I'm not on my dev machine right now. Once I get to that, I will post the function I was working on;

Answer

The following solution is a bit long winded but hopefully it will do the trick. I have not tested this approach on all possible scenarios so chances of mistakes in code is still good. The query will not properly handle the production of material that was never on the plan in the first place. Example if actual production included ‘D’ but ‘D’ is not in the plan.

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);


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]
            ,[Actual].[Order] AS [ActualOrder]
            ,[Plan].[Material]
            ,ABS([Plan].[Order] - [Actual].[Order]) AS [Distance] -- Distance between actual production order and planned production order.  
            ,[Plan].[Quantity] AS [PlannedQuantity]
            ,[Actual].[QuantityProduced] 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
                    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]
Comments