Here's a mod which works across different products with different numbers of ingredients:

USE tempdb

GO

CREATE TABLE Transactions(

trnKey INTEGER NOT NULL,

trnTranDate DATETIME NOT NULL,

trnProductIDINTEGER NOT NULL,

trnShipRecv CHAR(1) NOT NULL,

trnTranQty DECIMAL(18,5)NOT NULL,

trnTranUOM CHAR(2) NOT NULL,

PRIMARY KEY (trnKey)

)

CREATE TABLE Ingredients(

ingKey INTEGER NOT NULL,

ingIngrientDesc VARCHAR(20)NOT NULL,

PRIMARY KEY (ingKey)

)

CREATE TABLE Receipts(

recKey INTEGER NOT NULL,

recTrnKey INTEGER NOT NULL,

recIngKey INTEGER NOT NULL,

recIngPct DECIMAL(18,5) NOT NULL,

PRIMARY KEY (recKey)

)

INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 1,'Ingredient A'

INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 2,'Ingredient B'

INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 3,'Ingredient C'

INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 4,'Ingredient D'

INSERT INTO Transactions(trnKey, trnTranDate, trnProductID, trnShipRecv, trnTranQty, trnTranUOM)

SELECT 1, '1/1/2011', 123, 'R', 1000, 'GA' UNION ALL

SELECT 2, '1/2/2011', 123, 'S', -100, 'GA' UNION ALL

SELECT 3, '1/2/2011', 123, 'S', -200, 'GA' UNION ALL

SELECT 4, '1/3/2011', 123, 'R', 500, 'GA' UNION ALL

SELECT 5, '1/4/2011', 123, 'S', -100, 'GA' UNION ALL

SELECT 6, '1/1/2011', 124, 'R', 100, 'GA' UNION ALL

SELECT 7, '1/2/2011', 124, 'S', -50, 'GA' UNION ALL

SELECT 8, '1/3/2011', 124, 'R', 100, 'GA' UNION ALL

SELECT 9, '1/4/2011', 124, 'S', -150, 'GA'

INSERT INTO Receipts(recKey, recTrnKey, recIngKey, recIngPct)

SELECT 1, 1, 1, 75 UNION ALL

SELECT 2, 1, 2, 25 UNION ALL

SELECT 3, 4, 1, 80 UNION ALL

SELECT 4, 4, 2, 10 UNION ALL

SELECT 5, 4, 3, 10 UNION ALL

SELECT 6, 6, 1, 78 UNION ALL

SELECT 7, 6, 2, 22 UNION ALL

SELECT 8, 8, 1, 80 UNION ALL

SELECT 9, 8, 2, 10 UNION ALL

SELECT 10, 8, 3, 5 UNION ALL

SELECT 11, 8, 4, 5;

IF(OBJECT_ID('TempDB..#PreparedData') IS NOT NULL) DROP TABLE #PreparedData;

-- Prepare a temporary table which has everything we need in place to perform the calculation

;WITH ABC AS (

SELECT

seq = ROW_NUMBER() OVER(ORDER BY t.trnProductID, t.trnKey, ingIngrientDesc),

NewKey = DENSE_RANK() OVER (PARTITION BY t.trnProductID ORDER BY t.trnProductID, t.trnTranDate, t.trnShipRecv),

ingCount = COUNT(*) OVER(PARTITION BY t.trnProductID, t.trnKey),

t.trnKey,

t.trnTranDate,

t.trnProductID,

t.trnShipRecv,

t.trnTranQty,

t.trnTranUOM,

i.ingIngrientDesc,

r.recIngPct

FROM Transactions t

INNER JOIN (

SELECT trnProductID, ingKey, ingIngrientDesc

FROM Transactions t

INNER JOIN Receipts r ON r.recTrnKey = t.trnKey

INNER JOIN Ingredients i ON i.ingKey = r.recIngKey

GROUP BY trnProductID, ingKey, ingIngrientDesc

) i ON i.trnProductID = t.trnProductID

LEFT JOIN Receipts r ON r.recTrnKey = t.trnKey AND i.ingKey = r.recIngKey

)

SELECT

seq,

NewKey,

ingCount,

trnKey,

trnTranDate,

trnProductID,

trnShipRecv,

trnTranQty,

trnTranUOM,

ingIngrientDesc,

recIngPct= CASE WHEN trnShipRecv = 'R' THEN ISNULL(recIngPct,0) END,

[Ingr Qty]= CAST(CASE WHEN trnShipRecv = 'R' THEN ISNULL(trnTranQty*recIngPct,0)/100.00000 END AS DECIMAL(18,5)),

[Ingr Pct]= CAST(CASE WHEN trnShipRecv = 'R' THEN ISNULL(recIngPct,0) END AS DECIMAL(9,5)),

[Inventory Qty] = CAST(CASE WHEN NewKey = 1 THEN ISNULL(trnTranQty*recIngPct,0)/100.00000 ELSE NULL END AS DECIMAL(18,5)),

[Inventory Pct] = CAST(CASE WHEN NewKey = 1 THEN ISNULL(recIngPct,0) ELSE NULL END AS DECIMAL(9,5)),

TotalVolume= CASE WHEN NewKey = 1 THEN trnTranQty ELSE NULL END

INTO #PreparedData

FROM ABC

ORDER BY trnProductID, trnKey, ingIngrientDesc

-- This index is required for sensible performance

CREATE UNIQUE CLUSTERED INDEX [CX_seq] ON #PreparedData (seq ASC)

--SELECT * FROM #PreparedData -- 31 rows

-- run the calculation: the result is an output set.

;WITH Calculator AS (

SELECT

seq, NewKey, ingCount, trnKey, trnTranDate, trnProductID,

trnShipRecv, trnTranQty, trnTranUOM, ingIngrientDesc, recIngPct,

[Ingr Qty],

[Ingr Pct],

[Inventory Qty],

[Inventory Pct],

TotalVolume

FROM #PreparedData

WHERE trnKey = 1 -- seq IN (1,2,3) -- first 3 rows are first block for this product

UNION ALL

SELECT

tr.seq, tr.NewKey, tr.ingCount, tr.trnKey, tr.trnTranDate, tr.trnProductID,

tr.trnShipRecv, tr.trnTranQty, tr.trnTranUOM, tr.ingIngrientDesc, tr.recIngPct,

[Ingr Qty] = CAST(CASE

WHEN tr.trnShipRecv = 'R' THEN tr.[Ingr Qty]

WHEN tr.trnShipRecv = 'S' THEN tr.trnTranQty*lr.[Inventory Pct]/100.00000

END AS DECIMAL(18,5)),

[Ingr Pct] = CAST(CASE

WHEN tr.trnShipRecv = 'R' THEN tr.[Ingr Pct]

WHEN tr.trnShipRecv = 'S' THEN lr.[Inventory Pct]

END AS DECIMAL(9,5)),

[Inventory Qty] = CAST(CASE

WHEN tr.trnShipRecv = 'R' AND tr.NewKey = 1 THEN tr.[Ingr Qty]

WHEN tr.trnShipRecv = 'R' THEN lr.[Inventory Qty]+tr.[Ingr Qty]

WHEN tr.trnShipRecv = 'S' THEN lr.[Inventory Qty]+(tr.trnTranQty*lr.[Inventory Pct]/100.00000)

END AS DECIMAL(18,5)),

[Inventory Pct] = CAST(CASE

WHEN tr.trnShipRecv = 'R' AND tr.NewKey = 1 THEN tr.[Inventory Pct]

WHEN tr.trnShipRecv = 'R' THEN 100.00000*(lr.[Inventory Qty]+tr.[Ingr Qty]) / (tr.trnTranQty+lr.[TotalVolume])

WHEN tr.trnShipRecv = 'S' THEN lr.[Inventory Pct]

END AS DECIMAL(9,5)),

TotalVolume= CAST(CASE

WHEN tr.trnShipRecv = 'R' AND tr.NewKey = 1 THEN tr.trnTranQty

ELSE tr.trnTranQty+lr.[TotalVolume]

END AS DECIMAL(18,5))

FROM #PreparedData tr -- this set

INNER JOIN Calculator lr -- last set

ON lr.seq + tr.ingCount = tr.seq -- n rows at a time, where n = max number of ingredients for this product

) SELECT

[Date]= trnTranDate,

[Ship/Recv] = trnShipRecv,

[Product]= trnProductID,

[Tran Qty]= trnTranQty,

[UOM]= trnTranUOM,

[Ingredient]= ingIngrientDesc,

[Ingr Pct],

[Ingr Qty],

[Inventory Qty],

[Inventory Pct]

FROM Calculator

WHERE [Ingr Qty] <> 0

ORDER BY seq

-- About 3000 rows/s

DROP TABLE Receipts

DROP TABLE Transactions

DROP TABLE Ingredients