SQL - Subtracting a depleting value from rows

Question

I have a situation where I need to take a "quantity consumed" from one table, and apply it against a second table that has 1 or more rows that are "pooled lots" of quantities. I'm not sure how to describe it better, here's what I mean from a table perspective:

Table Pooled_Lots
----------------------------
Id  Pool    Lot Quantity
1   1       1   5
2   1       2   10
3   1       3   4
4   2       1   7
5   3       1   1
6   3       2   5

Table Pool_Consumption
----------------------------
Id  PoolId  QuantityConsumed
1   1       17
2   2       8
3   3       10

I need a resulting rowset from a SQL query that would look like:

Pool    Lot Quantity    QuantityConsumed    RunningQuantity RemainingDemand SurplusOrDeficit
1       1   5           17                  0               12              NULL
1       2   10          17                  0               2               NULL
1       3   4           17                  2               0               2
2       1   7           8                   0               1               -1
3       1   1           10                  0               9               NULL
3       2   5           10                  0               4               -4

So, Pool_Consumption.QuantityConsumed needs to be a "depleting value" subtracted over the rows from Pooled_Lots where Pool_Consumption.PoolId = Pooled_Lots.Pool. I can't figure out how you would state a query that says:

• If not on the last row, AmtConsumedFromLot = Quantity - QuantityConsumed if QuantityConsumed < Quantity, else Quantity
• If more rows, QuantityConsumed = QuantityConsumed - Quantity
• Loop until last row
• If last row, AmtConsumedFromLot = QuantityConsumed

Assume Id is a primary key, and the target DB is SQL 2005.

Edit: Since people are proclaiming I am "not giving enough information, please close this" Here is more: There is NO set lot that the Pool_Consumption draws from, it needs to draw from all lots where Pool_Consumption.PoolId = Pooled_Lots.Pool, until QuantityConsumed is either completely depleted or I am subtracting against the last subset of Pooled_Lots rows where Pool_Consumption.PoolId = Pooled_Lots.Pool

I don't know how more to explain this. This is not a homework question, this is not a made-up "thought exercise". I need help trying to figure out how to properly subtract QuantityConsumed against multiple rows!

Answer 1

Habo's answer looks like it works well, but wanted to provide a variation that doesn't require recursion but is still set-based. Without recursion, the solution is simpler and will probably perform better (with the right indexing strategy)

/*Raw data setup*/
CREATE TABLE #Pool_Consumption (Id int, [Pool] int, QuantityConsumed int );
CREATE TABLE #Pooled_Lots( Id int, [Pool] int, Lot int, Quantity int );

INSERT INTO #Pool_Consumption ( Id, [Pool], QuantityConsumed )
VALUES (1,1,17),(2,2,8),(3,3,10);

INSERT INTO #Pooled_Lots
VALUES (1,1,1,5),(2,1,2,10),(3,1,3,4),
(4,2,1,7),
(5,3,1,1),(6,3,2,5);


/*Setup work tables
Essentially ranges number each consumption and reserves a range of ID's for each lot*/
SELECT *
    ,LowerRange = 1
    ,UpperRange = QuantityConsumed
INTO #Pool_Consumption_Range
FROM #Pool_Consumption

SELECT *
    ,LowerRange = SUM(Quantity) OVER (PARTITION BY [Pool] ORDER BY Lot) - Quantity + 1
    ,UpperRange = SUM(Quantity) OVER (PARTITION BY [Pool] ORDER BY Lot) 
INTO #Pooled_Lots_Range
FROM #Pooled_Lots

SELECT 
     C.[Pool]
    ,L.Lot
    ,L.Quantity
    ,C.QuantityConsumed
    ,QtyFulfilled = SUM(CA.ReservedQty) OVER (PARTITION BY C.[Pool] ORDER BY L.ID)
    ,RemainderInLot = L.Quantity - CA.ReservedQty
    ,RemainingDemand = QuantityConsumed - SUM(CA.ReservedQty) OVER (PARTITION BY C.[Pool] ORDER BY L.ID) 
    ,SurplusOrDefecit = CASE WHEN L.ID = MAX(L.ID) OVER (PARTITION BY C.[Pool]) THEN SUM(L.Quantity) OVER (PARTITION BY C.[Pool] ORDER BY L.ID)  - C.QuantityConsumed END
FROM #Pool_Consumption_Range AS C
LEFT JOIN #Pooled_Lots_Range AS L
    ON C.[Pool] = L.[Pool]
    AND (
        /*Overlap between consumption range and lots ranges*/
        C.LowerRange BETWEEN L.LowerRange and L.UpperRange
        OR C.UpperRange BETWEEN L.LowerRange and L.UpperRange
        /*For when entire lots range between consumption range*/
        OR L.UpperRange BETWEEN C.LowerRange AND C.UpperRange
        )
CROSS APPLY (
    /*Finds the count of overlap between order range and inventory range
    Essentially orders all range values and finds the two middle numbers, then calculates the delta of them
    Ex. If you have a range of 2 to 5 and 3 to 6
        It sorts the numbers as 2,3,5,6
        It grabs the second value 3 (OFFSET 1 FETCH 1), then because LEAD runs before OFFSET it grabs the next row so 5
        The delta of 3 and 5 = 2 so there's a 2 number overlap between the two ranges
    */
    SELECT ReservedQty = LEAD(DTA.Points,1) OVER (ORDER BY DTA.Points) - DTA.Points + 1
    FROM (VALUES(C.LowerRange),(C.UpperRange),(L.LowerRange),(L.UpperRange)) AS DTA(Points)
    ORDER BY Points
    OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
            ) AS CA
ORDER BY C.[Pool],L.Lot

Answer 2

Left as an exercise to the OP: Figuring out the correct results given the sample data and summarizing the results of the following query:

-- Create some test data.
declare @Pooled_Lots as table ( Id int, Pool int, Lot int, Quantity int );
insert into @Pooled_Lots ( Id, Pool, Lot, Quantity ) values
  ( 1, 1, 1, 5 ), ( 2, 1, 2, 10 ), ( 3, 1, 3, 4 ),
  ( 4, 2, 1, 7 ),
  ( 5, 3, 1, 1 ), ( 6, 3, 2, 5 );
declare @Pool_Consumption as table ( Id int, Pool int, QuantityConsumed int );
insert into @Pool_Consumption ( Id, Pool, QuantityConsumed ) values
  ( 1, 1, 17 ), ( 2, 2, 8 ), ( 3, 3, 10 );

select * from @Pooled_Lots order by Pool, Lot;
select * from @Pool_Consumption order by Pool;

with Amos as (
  -- Start with Lot 1 for each Pool.
  select PL.Pool, PL.Lot, PL.Quantity, PC.QuantityConsumed,
    case
      when PC.QuantityConsumed is NULL then PL.Quantity
      when PL.Quantity >= PC.QuantityConsumed then PL.Quantity - PC.QuantityConsumed
      when PL.Quantity < PC.QuantityConsumed then 0
      end as RunningQuantity,
    case
      when PC.QuantityConsumed is NULL then 0
      when PL.Quantity >= PC.QuantityConsumed then 0
      when PL.Quantity < PC.QuantityConsumed then PC.QuantityConsumed - PL.Quantity
      end as RemainingDemand
    from @Pooled_Lots as PL left outer join
      @Pool_Consumption as PC on PC.Pool = PL.Pool
    where Lot = 1
  union all
  -- Add the next Lot for each Pool.
  select PL.Pool, PL.Lot, PL.Quantity, CTE.QuantityConsumed,
    case
      when CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand then CTE.RunningQuantity + PL.Quantity - CTE.RemainingDemand
      when CTE.RunningQuantity + PL.Quantity < CTE.RemainingDemand then 0
      end,
    case
      when CTE.RunningQuantity + PL.Quantity >= CTE.RemainingDemand then 0
      when CTE.RunningQuantity + PL.Quantity < CTE.RemainingDemand then CTE.RemainingDemand - CTE.RunningQuantity - PL.Quantity
      end
    from Amos as CTE inner join
      @Pooled_Lots as PL on PL.Pool = CTE.Pool and PL.Lot = CTE.Lot + 1
  )
select *,
  case
    when Lot = ( select max( Lot ) from @Pooled_Lots where Pool = Amos.Pool ) then RunningQuantity - RemainingDemand
    else NULL end as SurplusOrDeficit
  from Amos
  order by Pool, Lot;

Answer 3

(based on version 4 of question as my WiFi went down for quite some time)

(SELECT
  Pool,
  SUM(Quantity) as Pool_Quantity
FROM
  Pooled_Lots
GROUP BY
  Pool) as Pool_Quantity_Table

Now you have a table with the Pool Quantity rolled up into a single value.

Now the complete query:

SELECT
  Pool_Consumption.PoolID as Pool,
  Pool_Quantity_Table.Pool_Quantity as Quantity,
  Pool_Consumption.QuantityConsumed as AmtConsumedFromLot,
  (Pool_Quantity_Table.Pool_Quantity - Pool_Consumption.QuantityConsumed) as SurplusOrDefecit
FROM
  Pool_Consumption
INNER JOIN
  (SELECT
    Pool,
    SUM(Quantity) as Pool_Quantity
  FROM
    Pooled_Lots
  GROUP BY
    Pool) as Pool_Quantity_Table
ON (Pool_Consumption.PoolID = Pool_Quantity_Table.Pool);