Hey, just wanted to thank you guys for this thread. I have been working on a weighted average cost calculation for two or three days, and stumbled across this link. I've been trying to avoid doing a cursor calculation as I have over 300,000 different inventory items I have to walk through (over 73 million rows total). As you can see from the comments I left in, I shamelessly copied Jeff's code and modified it to fit my needs. Thanks a million.

Randy Stone

CREATE TABLE #WACTran(

[TranOrder]INT PRIMARY KEY CLUSTERED,

[TranType]VARCHAR(3) NULL,

[Qty]INT NULL,

[UnitCost]DECIMAL(14, 4) NULL,

[ExtendedCost]DECIMAL(14, 4) NULL,

[QtyOnHand]INT

)

INSERT INTO [#WACTran](TranOrder, TranType, Qty, UnitCost, ExtendedCost)

VALUES

(0,'BB',100,1.5000,150.0000 )

,(1,'S',-50,0.0000,0.0000 )

,(2,'RCT',15,0.0000,0.0000 )

,(3,'S',-5,0.0000,0.0000 )

,(4,'VC',15,1.6000,0.0000 )

,(5,'S',-5,0.0000,0.0000 )

,(6,'TO',-4,0.0000,0.0000 )

,(7,'TI',2,0.0000,0.0000 )

,(8,'VTI',2,1.6500,0.0000 )

,(9,'RCT',10,0.0000,0.0000 )

,(10,'S',-5,0.0000,0.0000 )

,(11,'VC',10,1.7500,0.0000 )

,(12,'S',-5,0.0000,0.0000 )

,(13,'TO',-4,0.0000,0.0000 )

,(14,'TI',2,0.0000,0.0000 );

--===== Declare some obviously named variables

DECLARE @PrevTranOrderINT,

@PrevQtyOnHandINT,

@PrevUnitCostDECIMAL(14,4),

@PrevExtendedCostDECIMAL(14,4)

--===== Do the "quirky" update using a very high speed pseudo-cursor,

-- This is very similar to what you would do in a language like "C" except the

-- "Read a row/Write a row" is built into the update.

UPDATE #WACTran

SET @PrevQtyOnHand = QtyOnHand = CASE

WHEN TranType = 'BB'

THEN Qty

WHEN TranType IN ('VTI','VC')

THEN @PrevQtyOnHand

ELSE @PrevQtyOnHand + Qty

END,

@PrevUnitCost = UnitCost = CASE

WHEN TranType = 'BB'

THEN UnitCost

WHEN TranType IN ('VTI','VC')

THEN (((@PrevQtyOnHand - Qty)*@PrevUnitCost)+(Qty*UnitCost))/@PrevQtyOnHand

ELSE @PrevUnitCost

END,

@PrevExtendedCost = ExtendedCost = CASE

WHEN TranType = 'BB'

THEN ExtendedCost

ELSE @PrevUnitCost*@PrevQtyOnHand

END,

@PrevTranOrder = TranOrder --Just an "anchor", but gotta have it to guarantee things.

FROM [#WACTran] WITH (INDEX(0), TABLOCKX)

--===== Display the results

SELECT *

FROM [#WACTran]

ORDER BY TranOrder

DROP TABLE #WACTran;