Ich habe vom FIFO den Lagerbestand dem Verkaufsauftrag zugeordnet, basierend auf der folgenden Auftragsart:

DECLARE @tblOrder TABLE
    (DealerCode NVARCHAR(50),
     PartCode NVARCHAR(50),
     OrderQty INT,
     OrderType NVARCHAR(50)
    )
INSERT  INTO @tblOrder
        ( DealerCode,
          PartCode,
          OrderQty,
          OrderType )
VALUES  ('D1','A',19,'Urgent'),
('D2','B',10,'Normal'),
('D3','C',11,'HotLine'),
('D1','D',20,'Normal'),
('D2','E',12,'Normal'),
('D2','D',40,'Normal');

DECLARE @tblStock TABLE
    (PartCode NVARCHAR(50),
     StockQty INT)
INSERT  INTO @tblStock
        ( PartCode,
          StockQty)
VALUES  ('A',20),
('B',15),
('C',9),
('D',30),
('E',0)

;WITH ordertemp AS (
select ord.dealercode, 
      ord.partcode, 
      ord.orderqty, 
      ord.ordertype,
      ROW_NUMBER() OVER (ORDER BY case ord.ordertype when 'HotLine' then 1 when 'Urgent' then 2 else 3 end, ord.partcode, ord.dealercode) 'StockPriority',
      sto.stockqty 'InitialStock'
from @tblorder ord
left outer join @tblstock sto
on ord.partcode = sto.partcode )

SELECT
    Orders.dealercode,
    Orders.partcode,
    CASE    WHEN Backlog.PriorQty > Orders.InitialStock THEN 0
            ELSE Orders.InitialStock - Backlog.PriorQty END 'Stock',
    Orders.orderqty,
    CASE    WHEN Backlog.PriorQty + Orders.OrderQty < Orders.InitialStock THEN Orders.OrderQty 
            WHEN Backlog.PriorQty > Orders.InitialStock THEN 0
            ELSE Orders.InitialStock - Backlog.PriorQty END 'Allocated',
    Orders.ordertype
FROM 
    ordertemp Orders
    INNER JOIN
    (
    SELECT A.stockpriority, A.partcode, ISNULL(SUM(B.orderqty),0) 'PriorQty'
    from ordertemp A
    LEFT OUTER JOIN ordertemp B
    on A.partcode = B.partcode
    and A.stockpriority > B.stockpriority 
    group by A.stockpriority, A.partcode ) Backlog
    ON Orders.stockpriority = Backlog.stockpriority
ORDER BY Orders.StockPriority

das Ergebnis

dealercode  partcode    Stock   orderqty    Allocated   ordertype
D3  C   9   11  9   HotLine
D1  A   20  19  19  Urgent
D2  B   15  10  10  Normal
D1  D   30  20  20  Normal
D2  D   10  40  10  Normal
D2  E   0   12  0   Normal

Ich würde es vorziehen, gleich zugeteilt. Ex. Artikelcode D (Lager = 30, Bestellmenge 20 & 40 => Zugeordnet (15,15). Bitte helfen Sie mir.

AKTUALISIEREN Die aktualisierte Logik wird jedem Teil einmalig mit Menge = 1 zugewiesen und durchläuft alle Teile, bis stock = 0

DECLARE @tblOrder TABLE
    (DealerCode NVARCHAR(50),
     PartCode NVARCHAR(50),
     OrderQty INT,
     OrderType NVARCHAR(50)
    )
INSERT  INTO @tblOrder
        ( DealerCode,
          PartCode,
          OrderQty,
          OrderType )
VALUES  ('D1','A',19,'Urgent'),
('D2','A',10,'Normal'),
('D3','A',11,'HotLine'),
('D1','A',20,'Normal'),
('D2','A',12,'Normal'),
('D1','A',40,'Normal');

DECLARE @tblStock TABLE
    (PartCode NVARCHAR(50),
     StockQty INT)
INSERT  INTO @tblStock
        ( PartCode,
          StockQty)
VALUES  ('A',100);

;WITH ordertemp AS (
select ord.dealercode, 
      ord.partcode, 
      ord.orderqty, 
      ord.ordertype,
      ROW_NUMBER() OVER (ORDER BY case ord.ordertype when 'HotLine' then 1 when 'Urgent' then 2 else 3 end, ord.partcode, ord.dealercode) 'StockPriority',
      sto.stockqty 'InitialStock'
from @tblorder ord
left outer join @tblstock sto
on ord.partcode = sto.partcode )

SELECT
    Orders.dealercode,
    Orders.partcode,
    CASE    WHEN Backlog.PriorQty > Orders.InitialStock THEN 0
            ELSE Orders.InitialStock - Backlog.PriorQty END 'Stock',
    Orders.orderqty,
    CASE    WHEN Backlog.PriorQty + Orders.OrderQty < Orders.InitialStock THEN Orders.OrderQty 
            WHEN Backlog.PriorQty > Orders.InitialStock THEN 0
            ELSE Orders.InitialStock - Backlog.PriorQty END 'Allocated',
    Orders.ordertype
FROM 
    ordertemp Orders
    INNER JOIN
    (
    SELECT A.stockpriority, A.partcode, ISNULL(SUM(B.orderqty),0) 'PriorQty'
    from ordertemp A
    LEFT OUTER JOIN ordertemp B
    on A.partcode = B.partcode
    and A.stockpriority > B.stockpriority 
    group by A.stockpriority, A.partcode ) Backlog
    ON Orders.stockpriority = Backlog.stockpriority
ORDER BY Orders.StockPriority

Ergebnis der FIFO-Zuordnung nach Bestellmenge:

dealercode  partcode    Stock   orderqty    Allocated   ordertype
D3  A   100 11  11  HotLine
D1  A   89  19  19  Urgent
D1  A   70  20  20  Normal
D1  A   50  40  40  Normal
D2  A   10  12  10  Normal
D2  A   0   10  0   Normal

Ich bevorzuge es, FIFOs mit orderty = 1 zuzuweisen, damit das Ergebnis genauer ist:

dealercode  partcode    Stock   orderqty    Allocated   ordertype
D3  A   100 11  11  HotLine
D1  A   89  19  19  Urgent
D1  A   70  20  20  Normal
D1  A   50  40  28  Normal
D2  A   22  12  12  Normal
D2  A   10  10  10  Normal

in Excel, zugewiesen durch Menge = 1 für alle Bestellungen: