split a row value according to a specific column

Question

I have total value in table A and need to get row value in table B until reached value in table A.

Table A

ID	Available
1	99
2	0

Table B

ID	Rack	RequiredQty
1	A	60
1	B	102
1	C	8
2	A	10

Expected Results

ID	Rack	RequiredQty	FulfilledQty
1	A	60	60
1	B	102	39
1	C	8	0
2	A	10	0

Answer 1

You can use a recursive CTE to produce a running total and subtract that from the required quantity:

;WITH CTE AS (
  SELECT ID, Rack, Required
    , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Rack ASC) AS Rn
  FROM TableB
), CTE2 AS (
  SELECT b.ID
    , b.Rn
    , b.Rack
    , b.Required
    , CASE
        WHEN b.Required > a.Available THEN a.Available
        ELSE a.Available - (a.Available - b.Required) 
      END AS Fulfilled
    , CASE
        WHEN b.Required > a.Available THEN 0
        ELSE a.Available - (a.Available - (a.Available - b.Required))
      END AS Available
  FROM CTE b
  INNER JOIN TableA a ON a.ID = b.ID
  WHERE b.Rn = 1
  UNION ALL
  SELECT b.ID
    , b.Rn
    , b.Rack
    , b.Required
    , CASE
        WHEN b.Required > a.Available THEN a.Available
        ELSE a.Available - (a.Available - b.Required) 
      END AS Fulfilled
    , CASE
        WHEN b.Required > a.Available THEN 0
        ELSE a.Available - (a.Available - (a.Available - b.Required))
      END AS Available
  FROM CTE b
  INNER JOIN CTE2 a ON a.ID = b.ID AND a.Rn = b.Rn-1
  WHERE b.Rn > 1
)

SELECT ID
  , Required AS RequiredQty
  , Fulfilled AS FulfilledQty
FROM CTE2
ORDER BY ID, Rack

You can see a working example in this fiddle, which produces the below output:

ID	Rack	RequiredQty	FulfilledQty
1	A	60	60
1	B	102	39
1	C	8	0
2	A	10	0