declare @temptable TABLE
( [product] nvarchar (35),
[q jan 2011] numeric (10,4), [jan 2011] numeric (10,4),
[q feb 2011] numeric (10,4), [feb 2011] numeric (10,4) , 
[q mar 2011] numeric (10,4), [mar 2011] numeric (10,4),
[q apr 2011] numeric (10,4),  [apr 2011] numeric (10,4),
[q may 2011] numeric (10,4),  [may 2011] numeric (10,4), 
[q jun 2011] numeric (10,4),  [jun 2011] numeric (10,4),
[q jul 2011] numeric (10,4), [jul 2011] numeric (10,4),
[q aug 2011] numeric (10,4), [aug 2011] numeric (10,4),
[q sept 2011] numeric (10,4), [sept 2011] numeric (10,4),
[q oct 2011] numeric (10,4), [oct 2011] numeric (10,4),
[q nov 2011] numeric (10,4), [nov 2011] numeric (10,4),
[q dec 2011] numeric (10,4), [dec 2011] numeric (10,4),
[q jan 2012] numeric (10,4), [jan 2012] numeric (10,4),
[q feb 2012] numeric (10,4), [feb 2012] numeric (10,4),
[sales feb 2012] numeric (10,4), [end of feb 2012 stock] numeric (10,4) )

insert into @temptable 
select 'PF10100',95.8,7.0989,476.7,6.9892,985,6.9947,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1092.3,8.5255,1000,1455.1

/*
	Solution by SwePeso
*/

-- Create a normalized staging table
CREATE TABLE	#Temp
		(
			Product NVARCHAR(35) NOT NULL,
			SeqID INT NOT NULL,
			Quantity MONEY NOT NULL,
			Cost MONEY NOT NULL,
			PRIMARY KEY
			(
				Product,
				SeqID
			)
		)

-- Populate the staging table
INSERT	#Temp
	(
		Product,
		SeqID,
		Quantity,
		Cost
	)
SELECT	Product,
	ROW_NUMBER() OVER (PARTITION BY Product ORDER BY thePeriod DESC) - 1 AS SeqID,
	Quantity,
	Cost
FROM	(
		SELECT Product, CAST('20110101' AS DATETIME) AS thePeriod, [q jan 2011] AS Quantity, [jan 2011] AS Cost FROM @TempTable UNION ALL
		SELECT Product, '20110201', [q feb 2011], [feb 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110301', [q mar 2011], [mar 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110401', [q apr 2011], [apr 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110501', [q may 2011], [may 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110601', [q jun 2011], [jun 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110701', [q jul 2011], [jul 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110801', [q aug 2011], [aug 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110901', [q sept 2011], [sept 2011] FROM @TempTable UNION ALL
		SELECT Product, '20111001', [q oct 2011], [oct 2011] FROM @TempTable UNION ALL
		SELECT Product, '20111101', [q nov 2011], [nov 2011] FROM @TempTable UNION ALL
		SELECT Product, '20111201', [q dec 2011], [dec 2011] FROM @TempTable UNION ALL
		SELECT Product, '20120101', [q jan 2012], [jan 2012] FROM @TempTable UNION ALL
		SELECT Product, '20120201', [q feb 2012], [feb 2012] FROM @TempTable UNION ALL
		SELECT Product, '99991101', [end of feb 2012 stock], 0 FROM @TempTable UNION ALL
		SELECT Product, '99991201', [sales feb 2012], 0 FROM @TempTable 
	) AS d
WHERE	Quantity IS NOT NULL

-- Create a staging table for iteration
CREATE TABLE	#Iteration
		(
			Product NVARCHAR(35),
			SeqID INT NOT NULL,
			Quantity MONEY NOT NULL,
			Cost MONEY NOT NULL,
			PRIMARY KEY
			(
				Product,
				SeqID
			)
		)

-- Populate the iteration table with an initial value for each product
INSERT	#Iteration
	(
		Product,
		SeqID,
		Quantity,
		Cost
	)
SELECT	Product,
	SeqID,
	Quantity,
	Cost
FROM	#Temp
WHERE	SeqID = 1

-- Iterate until all products has a zero or a negative quantity value
WHILE EXISTS(SELECT * FROM #Iteration WHERE Quantity > 0)
	UPDATE		i
	SET		i.SeqID = t.SeqID,
			i.Quantity = i.Quantity - t.Quantity,
			i.Cost = t.Cost
	FROM		#Iteration AS i
	INNER JOIN	#Temp AS t ON t.Product = i.Product
				AND t.SeqID = i.SeqID + 1
	WHERE		i.Quantity > 0

-- Set all negative values to a positive value
UPDATE	#Iteration
SET	Quantity = ABS(Quantity)
WHERE	Quantity < 0

-- Insert a new row for each product with previous last known cost
INSERT		#Iteration
		(
			Product,
			SeqID,
			Quantity,
			Cost
		)
SELECT		t.Product,
		t.SeqID,
		t.Quantity - i.Quantity AS Quantity,
		w.Cost
FROM		#Iteration AS i
INNER JOIN	#Temp AS t ON t.Product = i.Product
			AND t.SeqID = 0
INNER JOIN	#Temp AS w ON w.Product = i.Product
			AND w.SeqID = i.SeqID + 1

-- Display the final result
SELECT		Product,
		SUM(Quantity) AS Total,
		SUM(Quantity * Cost) / SUM(Quantity) AS Average
FROM		#Iteration
GROUP BY	Product
ORDER BY	Product

-- Clean up
DROP TABLE	#Temp,
		#Iteration

quote:

---

Originally posted by SwePeso

Didn't notice that. That response was from my cell phone.

---

N 56°04'39.26"
E 12°55'05.63"

---

declare @TempTable TABLE( [Product] nvarchar (35),
[q jan 2011] numeric (10,4), [jan 2011] numeric (10,4),
[q feb 2011] numeric (10,4), [feb 2011] numeric (10,4) , 
[q mar 2011] numeric (10,4), [mar 2011] numeric (10,4),
[q apr 2011] numeric (10,4), [apr 2011] numeric (10,4),
[q may 2011] numeric (10,4), [may 2011] numeric (10,4), 
[q jun 2011] numeric (10,4), [jun 2011] numeric (10,4),
[q jul 2011] numeric (10,4), [jul 2011] numeric (10,4),
[q aug 2011] numeric (10,4), [aug 2011] numeric (10,4),
[q sept 2011] numeric (10,4), [sept 2011] numeric (10,4),
[q oct 2011] numeric (10,4), [oct 2011] numeric (10,4),
[q nov 2011] numeric (10,4), [nov 2011] numeric (10,4),
[q dec 2011] numeric (10,4), [dec 2011] numeric (10,4),
[q jan 2012] numeric (10,4), [jan 2012] numeric (10,4),
[q feb 2012] numeric (10,4), [feb 2012] numeric (10,4),
[sales feb 2012] numeric (10,4), [end of feb 2012 stock] numeric (10,4) )
insert into @TempTable 
select 'PF10100',95.8,7.0989,476.7,6.9892,985,6.9947,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1092.3,8.5255,1000,1455.1 union all
select 'PF10250',829.8,10.0279,0,0,252.6,11.6828,0,0,432.4,9.6261,807.6,9.7684,0,0,0,0,646.9,9.235,413.2,9.2947,0,0,0,0,0,0,620.2,9.0702,638.2,520.2

/*
	Solution by SwePeso
*/

-- Create a normalized staging table
CREATE TABLE	#Temp
		(
			Product NVARCHAR(35) NOT NULL,
			SeqID INT NOT NULL,
			Remaining MONEY,
			Quantity MONEY,
			Items MONEY,
			Cost MONEY,
			Countdown MONEY,
			PRIMARY KEY
			(
				Product,
				SeqID
			)
		)

-- Populate the staging table 
INSERT	#Temp
	(
		Product,
		SeqID,
		Quantity,
		Cost
	)
SELECT	Product,
	ROW_NUMBER() OVER (PARTITION BY Product ORDER BY thePeriod DESC) - 1 AS SeqID,
	Quantity,
	Cost
FROM	(
		SELECT Product, CAST('20110101' AS DATETIME) AS thePeriod, [q jan 2011] AS Quantity, [jan 2011] AS Cost FROM @TempTable UNION ALL
		SELECT Product, '20110201', [q feb 2011], [feb 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110301', [q mar 2011], [mar 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110401', [q apr 2011], [apr 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110501', [q may 2011], [may 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110601', [q jun 2011], [jun 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110701', [q jul 2011], [jul 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110801', [q aug 2011], [aug 2011] FROM @TempTable UNION ALL
		SELECT Product, '20110901', [q sept 2011], [sept 2011] FROM @TempTable UNION ALL
		SELECT Product, '20111001', [q oct 2011], [oct 2011] FROM @TempTable UNION ALL
		SELECT Product, '20111101', [q nov 2011], [nov 2011] FROM @TempTable UNION ALL
		SELECT Product, '20111201', [q dec 2011], [dec 2011] FROM @TempTable UNION ALL
		SELECT Product, '20120101', [q jan 2012], [jan 2012] FROM @TempTable UNION ALL
		SELECT Product, '20120201', [q feb 2012], [feb 2012] FROM @TempTable UNION ALL
		SELECT Product, '99991101', [end of feb 2012 stock], NULL FROM @TempTable UNION ALL
		SELECT Product, '99991201', [sales feb 2012], NULL FROM @TempTable
	) AS d
WHERE	Quantity <> 0

DECLARE	@SeqID INT = 2

-- Initialize staging table with starting values
UPDATE		t
SET		t.Remaining = q.Quantity + w.Quantity - t.Quantity,
		t.Items = w.Quantity - t.Quantity,
		t.Countdown = q.Quantity - ABS(w.Quantity - t.Quantity)
FROM		#Temp AS t
INNER JOIN	#Temp AS w ON w.Product = t.Product
			AND w.SeqID = 1
INNER JOIN	#Temp AS q ON q.Product = t.Product
			AND q.SeqID = 0
WHERE		t.SeqID = 2

-- Iterate until no more items to process
WHILE @@ROWCOUNT > 0
	BEGIN
		SET	@SeqID = @SeqID + 1

		UPDATE		t
		SET		t.Remaining =	CASE
							WHEN w.Items < 0 THEN w.Remaining
							ELSE w.Items
						END,
				t.Items =	CASE
							WHEN w.Items < 0 THEN t.Quantity
							ELSE w.Items - t.Quantity
						END,
				t.Countdown = w.Countdown - ABS(
									CASE
										WHEN w.Items < 0 THEN t.Quantity
										ELSE w.Items - t.Quantity
									END
								)
		FROM		#Temp AS t
		INNER JOIN	#Temp AS w ON w.Product = t.Product
					AND w.SeqID = t.SeqID - 1
		WHERE		t.SeqID = @SeqID
				AND w.Countdown > 0
	END

-- Delete unwanted rows
DELETE
FROM	#Temp
WHERE	Remaining IS NULL
	AND SeqID > 0

-- Correct last row per product
UPDATE		t
SET		t.Items =	CASE
					WHEN t.Quantity = t.Items THEN w.Quantity + q.Items
					ELSE q.Countdown
				END
FROM		#Temp AS t
INNER JOIN	#Temp AS w ON w.Product = t.Product
			AND w.SeqID = 0
INNER JOIN	#Temp AS q ON q.Product = t.Product
			AND q.SeqID = t.SeqID - 1
WHERE		t.Countdown < 0

-- Display the final result
SELECT		t.Product,
		SUM(ABS(t.Items) * t.Cost) / SUM(ABS(t.Items)) AS Average
FROM		#Temp AS t
INNER JOIN	(
			SELECT		Product,
					MIN(SeqID) AS SeqID
			FROM		#Temp
			WHERE		Items < 0
			GROUP BY	Product
		) AS w ON w.Product = t.Product
			AND w.SeqID <= t.SeqID
GROUP BY	t.Product
ORDER BY	t.Product

-- Clean up
DROP TABLE	#Temp