Check if data follows planned order

Question

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 1

This is my take to answer.

I assume that every new production batch moves a pointer to Orders down to some new position so that all orders above this pointer become inactive (not 'OPENED') and it's just the time to decide are they GOOD or BAD.
And then quantity produced is distributed among OPENED orders. Here i can imagine different tolerance logic to be applied. The script below allocates minimum possible qty for orders, trying to fulfill as much orders as it can.

So it's a loop on production batches. Note also i change source data a bit.

declare @Orders table (
      OrderId bigint not null identity(1,1) primary key clustered, 
      Material char(1) not null, 
      Quantity int not NULL, 
      ToleranceRange int not NULL,
      --
      QtyProduced int not NULL default 0,
      [Status] varchar(100) not NULL  default 'OPENED'
);
declare @MaterialsProduced table (
    Material char(1) not null ,
    QuantityProduced int not NULL,
    BatchNm int not NULL
);

insert @Orders (Material, Quantity, ToleranceRange) 
values 
  ('A',120,5)
, ('B',120,5)
, ('A',120,5)
, ('A',120,5) --added this row
, ('C',120,10)
, ('A',120,5)
, ('A',120,5)
, ('C',120,5) --added this row
insert @MaterialsProduced (Material,QuantityProduced, BatchNm)
values ('A',250,101 ) -- changed qty
,('B',113,102)
,('C',111,103)
,('A',353,104)

DECLARE @PMaterial CHAR(1);
DECLARE @PQuantity INT;

DECLARE prod CURSOR 
FORWARD_ONLY
FOR
    SELECT Material,QuantityProduced FROM @MaterialsProduced ORDER BY BatchNm;
OPEN prod;
FETCH NEXT FROM prod INTO @PMaterial,@PQuantity;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- 1. Find first OPENED order for @PMaterial
    DECLARE @pointer int = 0;
    SELECT @pointer=min(OrderId)
    FROM @Orders 
    WHERE Material = @PMaterial AND [Status]='OPENED';
    -- SELECT @pointer;

    -- 2. Close all orders above @pointer
    UPDATE @Orders
    SET [Status]= CASE WHEN QtyProduced < Quantity - ToleranceRange THEN 'BAD - behind the pointer' ELSE 'GOOD' END
    WHERE OrderId < @pointer AND [Status]='OPENED';

    -- 3. Distribute @PQuantity among first OPENED orders of @PMaterial type.
    -- May need to adjust tolerance logic
    UPDATE o1
    SET QtyProduced = QtyProduced + CASE WHEN deficit <= @PQuantity 
        THEN Quantity - ToleranceRange - QtyProduced 
        ELSE CASE WHEN deficit - (Quantity - ToleranceRange - QtyProduced) <= @PQuantity
            THEN @PQuantity - (deficit  - (Quantity - ToleranceRange - QtyProduced))
            ELSE 0 END
        END
        ,[Status] = CASE WHEN deficit <= @PQuantity 
            THEN CASE WHEN
                  -- if this is the last order available check for superflow
                  OrderId = (SELECT Max(OrderId) FROM @orders WHERE Material= @PMaterial)
                  AND @PQuantity - deficit > 2*ToleranceRange
                THEN 'BAD - superflow' 
                ELSE 'GOOD' END
            ELSE 'OPENED' END
    FROM @Orders o1
    CROSS APPLY (
        SELECT deficit = sum(o2.Quantity - o2.ToleranceRange - o2.QtyProduced)
        FROM @Orders o2
        WHERE o2.OrderId BETWEEN @pointer AND o1.OrderId 
            AND o2.Material = @PMaterial AND o2.[Status]='OPENED'
        ) t
    WHERE OrderId >= @pointer AND Material = @PMaterial AND [Status]='OPENED'; 

    FETCH NEXT FROM prod INTO @PMaterial,@PQuantity;
END
CLOSE prod;
DEALLOCATE prod;
-- Still OPENED orders are really BAD
UPDATE @Orders 
SET [Status] ='BAD - finally'
WHERE [Status] ='OPENED';

SELECT * FROM @Orders
ORDER BY OrderId;

The result is

OrderId Material    Quantity    ToleranceRange  QtyProduced Status
1   A   120 5   115 GOOD
2   B   120 5   113 BAD - behind the pointer
3   A   120 5   115 GOOD
4   A   120 5   20  BAD - behind the pointer
5   C   120 10  110 GOOD
6   A   120 5   115 GOOD
7   A   120 5   115 BAD - superflow
8   C   120 5   1   BAD - finally

EDIT

In order to take the rule

an "order" is open until the produced material changes, unless there are more of the same. So if I made 250 of material 'A' on batchNm 101, but planned 120 'A's and right after planned 120 'B', the 'A's would be a BAD - superflow

into account replace 3d step in the above script with

-- 3. Distribute @PQuantity among first sequential orders of @PMaterial type
-- may need to adjust tolerance logic
-- 3.1. Only continous sequence of the same material orders are allowed
DECLARE @lastInSeq int = 0;
SELECT @lastInSeq = isnull(min(OrderId)-1, @pointer)
FROM @Orders 
WHERE Material <> @PMaterial AND OrderId > @pointer;

UPDATE o1
SET QtyProduced = QtyProduced + CASE WHEN deficit <= @PQuantity 
    THEN Quantity - ToleranceRange - QtyProduced 
    ELSE CASE WHEN deficit - (Quantity - ToleranceRange - QtyProduced) <= @PQuantity
        THEN @PQuantity - (deficit  - (Quantity - ToleranceRange - QtyProduced))
        ELSE 0 END
    END
    ,[Status] = CASE WHEN deficit <= @PQuantity 
        THEN CASE WHEN
              -- if this is the last order available check for superflow
              OrderId = @lastInSeq
              AND @PQuantity - deficit > 2*ToleranceRange
            THEN 'BAD - superflow' -- superlow
            ELSE 'GOOD' END
        ELSE 'OPENED' END
FROM @Orders o1
CROSS APPLY (
    SELECT deficit = sum(o2.Quantity - o2.ToleranceRange - o2.QtyProduced)
    FROM @Orders o2
    WHERE o2.OrderId BETWEEN @pointer AND o1.OrderId 
        AND o2.Material = @PMaterial AND o2.[Status]='OPENED'
    ) t
WHERE OrderId BETWEEN @pointer AND @lastInSeq; 

This version result is

1   A   120 5   115 BAD - superflow
2   B   120 5   113 BAD - behind the pointer
3   A   120 5   0   BAD - behind the pointer
4   A   120 5   0   BAD - behind the pointer
5   C   120 10  110 GOOD
6   A   120 5   115 GOOD
7   A   120 5   115 BAD - superflow
8   C   120 5   0   BAD - finally

Answer 2

A LOOP is the best choice for this question.

declare @Orders table (
      OrderId bigint not null identity(1,1) primary key clustered, 
      Material char(1) not null, 
      Quantity int not NULL, 
      TolerenceRange int not NULL
)
declare @MaterialsProduced table (
    Material char(1) not null 
    , QuantityProduced int not NULL,
    OrderId int 
)

insert @Orders (Material, Quantity, TolerenceRange) 
values 
  ('A',120,5)
, ('B',120,5)
, ('A',120,5)
, ('C',120,10)
, ('A',120,5)
, ('A',120,5)
insert @MaterialsProduced (Material,QuantityProduced, OrderId)
values ('A',120,1 )
,('B',113, 2)
,('C',111,3 )
,('A',353, 4)

declare @result table (
    OrderId bigint, 
    Material char(1) not null, 
    Quantity int not NULL, 
    TolerenceRange int not NULL,
    Status NVARCHAR(10)
)

DECLARE @TempMaterialsProduced TABLE (OrderId INT)
DECLARE @Counter INT = 1

WHILE (@Counter <= (SELECT COUNT(1) FROM @Orders))
BEGIN

    IF EXISTS 
    (       
        SELECT * FROM @Orders O
        WHERE 
            O.OrderId = @Counter AND
            O.Material = (
                            SELECT TOP 1 M.Material FROM @MaterialsProduced M
                            WHERE
                                M.OrderId NOT IN (SELECT T.OrderId FROM @TempMaterialsProduced T)
                            ORDER BY M.OrderId
                         )            
    )
        BEGIN
            INSERT INTO @TempMaterialsProduced
            SELECT TOP 1 M.OrderId FROM @MaterialsProduced M
            WHERE
                M.OrderId NOT IN (SELECT T.OrderId FROM @TempMaterialsProduced T)
            ORDER BY M.OrderId

            --

            INSERT INTO @result
            SELECT 
                O.OrderId ,
                O.Material ,
                O.Quantity ,
                O.TolerenceRange, 
                CASE
                    WHEN
                        M.QuantityProduced >= O.Quantity - O.TolerenceRange THEN 'GOOD'                     
                    ELSE 'BAD' END [Status]
            FROM 
                @Orders O INNER JOIN 
                @MaterialsProduced M ON M.OrderId = (SELECT MAX(T.OrderId) FROM @TempMaterialsProduced T)
            WHERE 
                O.OrderId = @Counter

        END
    ELSE
        BEGIN

            INSERT INTO @result
            SELECT *, 'BAD' FROM @Orders O
            WHERE 
                O.OrderId = @Counter
        END

    SET @Counter += 1;    
END

SELECT * FROM @result

Output:

OrderId Material    Quantity    TolerenceRange  Status
1       A           120         5               GOOD
2       B           120         5               BAD
3       A           120         5               BAD
4       C           120         10              GOOD
5       A           120         5               GOOD
6       A           120         5               BAD

Answer 3

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


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]

Answer 4

Based on your update of including the OrderId in the second table, please find an updated solution below:

declare @Orders table (
    OrderId bigint not null identity(1,1) primary key clustered
    , Material char(1) not null
    , Quantity int not null
    , TolerenceRange int not null
)
declare @SatisfiedOrders  table (
    Material char(1) not null 
    , QuantityProduced int not null
    , RelatedOrderId bigint not null --foreign key references @Orders(OrderId) --if we weren't using table variables we could define a foreign key here
)

insert @Orders (Material, Quantity, TolerenceRange) 
values ('A',120,5)
, ('B',120,5)
, ('A',120,5)
, ('C',120,10)
, ('A',120,5)
, ('A',120,5)
insert @SatisfiedOrders (Material,QuantityProduced, RelatedOrderId)
values ('A',150, 1)
,('B',113, 2)
,('C',111, 3)
,('A',353, 4)

select o.Material, o.Quantity, o.TolerenceRange, o.OrderId
, case 
    when 
        o.Material = so.Material
        and so.QuantityProduced between o.Quantity - o.TolerenceRange and o.Quantity + o.TolerenceRange  
    then 'GOOD' 
    else 'BAD' 
  end [Status] 
, case 
    when so.RelatedOrderId is null then 'No Matching Order Found (Order #:' + cast(o.OrderId as nvarchar) + ')' 
    else '' 
        + case 
            when o.Material = so.Material then '' 
            else 'Different Materials (Ordered: ' + o.Material + '; Received: ' + so.Material + ').  ' 
          end
        + case 
            when so.QuantityProduced >= o.Quantity - o.TolerenceRange then '' 
            else 'Quantity below minimum (Ordered Min: ' + cast(o.Quantity - o.TolerenceRange as nvarchar) + '; Received: ' + cast(so.QuantityProduced as nvarchar) + ')' 
          end
        + case 
            when so.QuantityProduced <= o.Quantity + o.TolerenceRange then '' 
            else 'Quantity above maximum (Ordered Max: ' + cast(o.Quantity + o.TolerenceRange as nvarchar) + '; Received: ' + cast(so.QuantityProduced as nvarchar) + ')' 
          end
  end
  [Explanation] 
from @Orders o
left outer join @SatisfiedOrders so
on so.RelatedOrderId = o.OrderId
order by o.OrderId

Answer 5

Is this what you're after? If so I'll provide more info in the answer; just want to ensure this answer satisfies your question first though / happy to take questions in the comments in the meantime if you need more info before you can say.

declare @Orders table (
    OrderId bigint not null identity(1,1) primary key clustered
    , Material char(1) not null
    , Quantity int not null
    , TolerenceRange int not null
)
declare @MaterialsProduced table (
    Material char(1) not null 
    , QuantityProduced int not null
)

insert @Orders (Material, Quantity, TolerenceRange) 
values ('A',120,5)
, ('B',120,5)
, ('A',120,5)
, ('C',120,10)
, ('A',120,5)
, ('A',120,5)
/*
select * from @Orders

 Material | Quantity | TolerenceRange | OrderId |
+----------+----------+-------+-------+
| A        |      120 |     5 |     1 |
| B        |      120 |     5 |     2 |
| A        |      120 |     5 |     4 |
| C        |      120 |    10 |     3 |
| A        |      120 |     5 |     5 |
| A        |      120 |     5 |     6 |
+----------+----------+-------+-------+
*/
insert @MaterialsProduced (Material,QuantityProduced)
values ('A',150)
,('B',113)
,('C',111)
,('A',353)
/*
+----------+------------------+
| Material | QuantityProduced |
+----------+----------+-------+
| A        |      150 |
| B        |      113 |
| C        |      111 |
| A        |      353 |
*/

select o.Material, o.Quantity, o.TolerenceRange, o.OrderId
, case when o.Quantity - o.TolerenceRange <= p.TotalQuantityProduced  - sum(po.Quantity) then 'GOOD' else 'BAD' end [Status] --If we assume all previous orders are fully satisfied, can we satisfy this order based on minimum permitted amount
, case when o.Quantity - o.TolerenceRange <= p.TotalQuantityProduced  - sum(po.Quantity - po.TolerenceRange) then 'GOOD' else 'BAD' end [BestCaseStatus] --If we assume all previous orders take the minimum allowed amount, can we satisfy this order based on minimum permitted amount
, case when o.Quantity - o.TolerenceRange <= p.TotalQuantityProduced  - sum(po.Quantity + po.TolerenceRange) then 'GOOD' else 'BAD' end [WorstCaseStatus] --If we assume all previous orders take the maximum allowed amount, can we satisfy this order based on minimum permitted amount
from @Orders o
left outer join (select Material, sum(QuantityProduced) TotalQuantityProduced from @MaterialsProduced group by Material) p
    on p.Material = o.Material
left outer join @Orders po --previous orders
    on po.Material = o.Material
    and po.OrderId < o.OrderId
group by o.OrderId, o.Material, o.Quantity, o.TolerenceRange, p.TotalQuantityProduced 
order by o.OrderId