SQL Running total over the same key

Question

edit: As User Larna Requested a create/insert for the subquery tables I have added these as well.

The below query returns:

    DocNum  ItemCode    Planned Qty   WhsCode   BinCode    QuantityToPick
    901903  A00001      12.000000     98        98-A001    7.000000
    901903  A00001      12.000000     98        98-A002    5.000000  --sum of 5 and 7 makes 12
    901904  A00001      17.000000     98        98-A001    7.000000
    901904  A00001      17.000000     98        98-A002    6.000000
    901904  A00001      17.000000     98        98-A003    4.000000  --sum of 7,6 and 4 makes 17



But I want to have this result:

    DocNum  ItemCode    Planned Qty    WhsCode  BinCode    QuantityToPick
    901903  A00001      12.000000      98       98-A001    7.000000
    901903  A00001      12.000000      98       98-A002    5.000000  --sum of 5 and 7 makes 12

-- continue from bin code 98A002 for the new docnum as we had 5 out of 6 where consumed 
    901904  A00001      17.000000      98       98-A002    1.000000 
    901904  A00001      17.000000      98       98-A003    12.000000
    901904  A00001      17.000000      98       98-A004    4.000000  --sum of 1,12 and 4 makes 17

So basically I want to continue where I left off after using the first DocNum,ItemCode,Quantity to eat the first bin and the second bin partially and continue with the second DocNum,ItemCode,Quantity to "eat" remaining quantity of the second bin and then the other left over bins until I reach 0

But I'm a bit stuck on how to do this. Does anyone has any advice/solution?

-- Create Tables for Subquerys + insert relevant data

CREATE TABLE [dbo].[WOR1](-- Removed the unimportant columns
   [DocEntry] [int] NOT NULL,
   [LineNum] [int] NOT NULL,
   [ItemCode] [nvarchar](50) NULL,
   [PlannedQty] [numeric](19, 6) NULL,
   [IssuedQty] [numeric](19, 6) NULL,
   [wareHouse] [nvarchar](8) NULL
  )

INSERT INTO [dbo].[WOR1]
VALUES(154  ,0  ,'A00001'   ,12.000000  ,0.000000,  '01')
     ,(154  ,1  ,'P10001'   ,12.000000  ,0.000000,  '01')
     ,(154  ,2  ,'L10001'   ,12.000000  ,0.000000,  '01')
     ,(155  ,0  ,'A00001'   ,17.000000  ,0.000000,  '01')
     ,(155  ,1  ,'P10001'   ,17.000000  ,0.000000,  '01')
     ,(155  ,2  ,'L10001'   ,17.000000  ,0.000000,  '01')



CREATE TABLE [dbo].[OWOR](-- Removed the unimportant columns
       [DocEntry] [int] NOT NULL,
       [DocNum] [int] NOT NULL,
       [Status] [char](1) NULL
     )

INSERT INTO [dbo].[OWOR]
VALUES( 154,    901903, 'R')
     ,( 155,    901904, 'R')

CREATE TABLE [dbo].[OIBQ](-- Removed the unimportant columns
        [ItemCode] [nvarchar](50) NOT NULL,
        [BinAbs] [int] NOT NULL,
        [OnHandQty] [numeric](19, 6) NULL,
        [WhsCode] [nvarchar](8) NULL
)

INSERT INTO [dbo].[OIBQ]
VALUES('A00001',    7,  15.000000,  '98')
     ,('A00001',    2,  7.000000,   '98')
     ,('A00001',    3,  6.000000,   '98')
     ,('A00001',    4,  12.000000,  '98')



CREATE TABLE [dbo].[OBIN](-- Removed the unimportant columns
        [AbsEntry] [int] NOT NULL,
        [BinCode] [nvarchar](228) NOT NULL,
        [WhsCode] [nvarchar](8) NULL
      )

INSERT INTO [dbo].[OBIN]
VALUES(1,   '98-SYSTEM-BIN-LOCATION',   '98')
     ,(2,   '98-A001',  '98')
     ,(3,   '98-A002',  '98')
     ,(4,   '98-A003',  '98')
     ,(5,   '98-A004',  '98')
     ,(6,   '98-A005',  '98')
     ,(7,   '98-A006',  '98')

Query with subqueries:

SELECT

WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty', 
BINSTOCK.WhsCode,
BINSTOCK.BinCode,

CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity 
    THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) 
    ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty


FROM (
    SELECT IBQ.ItemCode, SUM(IBQ.OnHandQty)as 'Quantity',BIN.WhsCode,  BIN.BinCode ,
    SUM(SUM(OnHandQty)) OVER (PARTITION BY IBQ.ItemCode 
                                        ORDER BY IBQ.ItemCode,BIN.BinCode ) as 'RunningTotalQty'
    FROM OIBQ IBQ INNER JOIN OBIN BIN on IBQ.BinAbs = BIN.AbsEntry
    GROUP BY    IBQ.ItemCode, BIN.WhsCode,  BIN.BinCode 
) BINSTOCK
INNER JOIN ( 
    SELECT distinct WOR.DocNum, WOR1.LineNum, WOR1.ItemCode, WOR1.PlannedQty - WOR1.IssuedQty as 'Quantity' 
    FROM OWOR WOR 
    INNER JOIN  WOR1 WOR1 on WOR.DocEntry = WOR1.DocEntry 
    INNER JOIN OIBQ IBQ on IBQ.ItemCode = WOR1.ItemCode
    where WOR.Status = 'R'
) WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode 
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;

Query without subqueries:

-- SIMPLIFIED Version (Subquery results in tables BINSTOCK & WOPICK)

  CREATE TABLE [dbo].[BINSTOCK](
    ItemCode NVARCHAR(10) NOT NULL
    ,Quantity DECIMAL (19,6) NOT NULL
    ,WhsCode INT NOT NULL
    ,BinCode NVARCHAR (10) NOT NULL
    ,RunningTotalQty DECIMAL (19,6) NOT NULL
    )

 INSERT INTO [dbo].[BINSTOCK]
    VALUES('A00001' , 7.000000, 98, '98-A001' , 7.000000),
    ('A00001' , 6.000000, 98, '98-A002' , 13.000000),
    ('A00001' , 12.000000, 98, '98-A003' , 25.000000),
    ('A00001' , 15.000000, 98, '98-A006' , 40.000000)

 CREATE TABLE [dbo].[WOPICK](
    DocNum INT NOT NULL
    ,LineNum INT NOT NULL
    ,ItemCode NVARCHAR (10) NOT NULL
    ,Quantity DECIMAL (19,6) NOT NULL
    )

   INSERT INTO [dbo].[WOPICK]
    VALUES(901903,0,'A00001',12.000000),
    (901904,0,'A00001',17.000000)

---

SELECT
WOPICK.DocNum,
WOPICK.ItemCode,
WOPICK.Quantity as 'Planned Qty', 
BINSTOCK.WhsCode,
BINSTOCK.BinCode,

CASE WHEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) <BINSTOCK.Quantity 
    THEN (BINSTOCK.Quantity -(BINSTOCK.RunningTotalQty - WOPICK.Quantity)) 
    ELSE BINSTOCK.Quantity END as 'QuantityToPick'-- FinalQty


FROM  BINSTOCK
INNER JOIN WOPICK on BINSTOCK.ItemCode = WOPICK.ItemCode 
WHERE BINSTOCK.RunningTotalQty - BINSTOCK.Quantity < WOPICK.Quantity
order by WOPICK.LineNum;

Answer 1

This is actually easier than it first sounds. But you're going to want to wrap you're head around something first - you need running totals for the needed quantities as well:

SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
       SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode 
                                         ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
       SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode 
                                         ORDER BY OWOR.docEntry) AS runningTotalNeeded
      FROM OWOR
      JOIN WOR1
        ON WOR1.docEntry = OWOR.docEntry
      WHERE OWOR.status = 'R'

.... with the combination of the "previous" running-needed column (and assuming a previous total quantity), we're now looking at an overlapping range query.

In combination with (a slightly modified version of) your BINSTOCK:

SELECT *

FROM (SELECT itemCode, whsCode, binCode, quantity,
             SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) - quantity AS prevTotalQuantity, 
             SUM(quantity) OVER (PARTITION BY itemCode ORDER BY binCode) AS runningTotalQuantity
      FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
            FROM OIBQ
            JOIN OBIN
              ON OBIN.absEntry = OIBQ.binAbs
            GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty,
             SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
             SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode ORDER BY OWOR.docEntry) AS runningTotalNeeded
      FROM OWOR
      JOIN WOR1
        ON WOR1.docEntry = OWOR.docEntry
      WHERE OWOR.status = 'R') WOPICK
  ON WOPICK.itemCode = BINSTOCK.itemCode
     AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
     AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity

... which yields the following table:

itemCode | whsCode | binCode | quantity  | prevTotalQuantity | runningTotalQuantity | docNum | itemCode | plannedQty | prevTotalNeeded | runningTotalNeeded
:------- | :------ | :------ | :-------- | :---------------- | :------------------- | -----: | :------- | :--------- | :-------------- | :-----------------
A00001   | 98      | 98-A001 | 7.000000  | 0.000000          | 7.000000             | 901903 | A00001   | 12.000000  | 0.000000        | 12.000000         
A00001   | 98      | 98-A002 | 6.000000  | 7.000000          | 13.000000            | 901903 | A00001   | 12.000000  | 0.000000        | 12.000000         
A00001   | 98      | 98-A002 | 6.000000  | 7.000000          | 13.000000            | 901904 | A00001   | 17.000000  | 12.000000       | 29.000000         
A00001   | 98      | 98-A003 | 12.000000 | 13.000000         | 25.000000            | 901904 | A00001   | 17.000000  | 12.000000       | 29.000000         
A00001   | 98      | 98-A006 | 15.000000 | 25.000000         | 40.000000            | 901904 | A00001   | 17.000000  | 12.000000       | 29.000000         

....And then, with a judicious CASE statement, we can turn the three four (not three) conditions for the overlaps into our final totals:

SELECT WOPICK.docNum, WOPICK.itemCode, WOPICK.plannedQty,
       BINSTOCK.whsCode, BINSTOCK.binCode,
       CASE WHEN WOPICK.prevTotalNeeded < BINSTOCK.prevTotalQuantity
                 AND WOPICK.runningTotalNeeded > BINSTOCK.runningTotalQuantity
            THEN BINSTOCK.quantity
            WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
                 AND WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
            THEN WOPICK.neededQuantity
            WHEN WOPICK.runningTotalNeeded <= BINSTOCK.runningTotalQuantity
            THEN WOPICK.runningTotalNeeded - BINSTOCK.prevTotalQuantity
            WHEN WOPICK.prevTotalNeeded >= BINSTOCK.prevTotalQuantity
            THEN BINSTOCK.runningTotalQuantity - WOPICK.prevTotalNeeded
            END AS quantityToPick

FROM (SELECT itemCode, whsCode, binCode, quantity,
             SUM(quantity) OVER (PARTITION BY itemCode 
                                 ORDER BY binCode) - quantity AS prevTotalQuantity, 
             SUM(quantity) OVER (PARTITION BY itemCode 
                                 ORDER BY binCode) AS runningTotalQuantity
      FROM (SELECT OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode, SUM(OIBQ.onHandQty) AS quantity
            FROM OIBQ
            JOIN OBIN
              ON OBIN.absEntry = OIBQ.binAbs
            GROUP BY OIBQ.itemCode, OIBQ.whsCode, OBIN.binCode) TotalQuantity
) BINSTOCK
JOIN (SELECT OWOR.docNum, WOR1.itemCode, plannedQty, plannedQty - issuedQty AS neededQuantity,
             SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode 
                                               ORDER BY OWOR.docEntry) - (plannedQty - issuedQty) AS prevTotalNeeded,
             SUM(plannedQty - issuedQty) OVER (PARTITION BY WOR1.itemCode 
                                               ORDER BY OWOR.docEntry) AS runningTotalNeeded
      FROM OWOR
      JOIN WOR1
        ON WOR1.docEntry = OWOR.docEntry
      WHERE OWOR.status = 'R') WOPICK
  ON WOPICK.itemCode = BINSTOCK.itemCode
     AND WOPICK.prevTotalNeeded <= BINSTOCK.runningTotalQuantity
     AND WOPICK.runningTotalNeeded >= BINSTOCK.prevTotalQuantity

Fiddle example ...which yields the desired results:

docNum | itemCode | plannedQty | whsCode | binCode | quantityToPick
-----: | :------- | :--------- | :------ | :------ | :-------------
901903 | A00001   | 12.000000  | 98      | 98-A001 | 7.000000      
901903 | A00001   | 12.000000  | 98      | 98-A002 | 5.000000      
901904 | A00001   | 17.000000  | 98      | 98-A002 | 1.000000      
901904 | A00001   | 17.000000  | 98      | 98-A003 | 12.000000     
901904 | A00001   | 17.000000  | 98      | 98-A006 | 4.000000      

(ordering has been omitted, because the original one seemed unusual. It may be possible to remove the reference to OBIN inside of BINSTOCK, depending on your source data)

Answer 2

This is clearly a job for a stored procedure. It would be madness to even attempt it using a query. (Edit after Clockwork's answer: Doh!)

That said:

WITH Bin AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY BinCode) AS BinRank
  FROM BINSTOCK
), Pick AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemCode ORDER BY DocNum) AS PickRank
  FROM WOPICK
), BinPick AS (
  SELECT
    b.ItemCode,
    b.Quantity AS BinQty,
    b.BinCode,
    b.BinRank,
    p.Quantity AS PickQty,
    p.DocNum,
    p.PickRank,
    CAST (
      CASE
        WHEN b.Quantity < p.Quantity
        THEN b.Quantity
        ELSE p.Quantity
        END
    AS DECIMAL) AS Picked,
    CAST (
      CASE
        WHEN b.Quantity < p.Quantity
        THEN p.Quantity - b.Quantity
        ELSE 0
        END
    AS DECIMAL) AS Required,
    CAST (
      CASE
        WHEN b.Quantity < p.Quantity
        THEN 0
        ELSE b.Quantity - p.Quantity
        END
    AS DECIMAL) AS Remaining,
    CAST (
      CASE
        WHEN b.Quantity < p.Quantity
        THEN null
        ELSE b.BinRank
        END
    AS NVARCHAR) AS LastFullBinRank
  FROM Bin b
  JOIN Pick p ON p.ItemCode = b.ItemCode
  WHERE b.BinRank = 1
  AND p.PickRank = 1
  UNION ALL
  SELECT
    ItemCode,
    BinQty,
    BinCode,
    BinRank,
    PickQty,
    DocNum,
    PickRank,
    CAST (
      CASE WHEN Remaining < Required
        THEN Remaining
        ELSE Required
        END
      AS DECIMAL) AS Picked,
    CAST (
      CASE WHEN Remaining < Required
        THEN Required - Remaining
        ELSE 0
        END
    AS DECIMAL) AS Required,
    CAST (
      CASE WHEN Remaining < Required
        THEN 0
        ELSE Remaining - Required
        END
    AS DECIMAL) AS Remaining,
    CAST (
      CASE WHEN Remaining < Required
        THEN null
        ELSE BinRank
        END
    AS NVARCHAR) AS LastFullBinRank
  FROM (
    SELECT
      b.ItemCode,
      b.Quantity AS BinQty,
      b.BinCode,
      b.BinRank,
      p.Quantity AS PickQty,
      p.DocNum,
      p.PickRank,
      bp.DocNum AS PrevDocNum,
      bp.Picked,
      bp.LastFullBinRank,
      CASE WHEN p.DocNum = bp.DocNum
        -- Same order.  Continue with required from previous.
        THEN bp.Required
        -- New order.  Required is new order quantity.
        ELSE p.Quantity
        END
      AS Required,
      CASE WHEN p.DocNum = bp.DocNum
        -- Same order.  Hence fresh bin.  Use quanity from this bin.
        THEN b.Quantity
        -- New order.  Hence used bin.  Use remaining carried over.
        ELSE bp.Remaining
        END
      AS Remaining
      FROM BinPick bp
      JOIN Pick p
        ON p.ItemCode = bp.ItemCode
        AND (
          -- Order complete.  Move to next order
          (bp.Required = 0 AND p.PickRank = bp.PickRank + 1)
          OR
          -- Order incomlete.  Continue with current Order
          (bp.Required > 0 AND p.PickRank = bp.PickRank)
        )
      JOIN Bin b
        ON b.ItemCode = bp.ItemCode
        AND (
          -- Order complete.  Resume at last full bin.
          (bp.Required = 0 AND b.BinRank = bp.LastFullBinRank)
          OR
          -- Order incomlete.  Move to next bin.
          (bp.Required > 0 AND bp.LastFullBinRank IS NULL AND b.BinRank = bp.BinRank + 1)
        )
  ) sub1
) SELECT * FROM BinPick

Note: I've barely tested this. I'll leave it to those interested to correct any mistakes, but I think the principle is correct. Use a recursive CTE to identity the next Bin/Order and maintain a bunch of variables in columns to keep track of your working!

See DB Fiddle