SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sum until value reached and calculated result		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tempus
Starting Member

42 Posts
Posted - 03/16/2012 :  03:01:59  Show Profile  Reply with Quote
hello,

i am having trouble to calculate a value . having the following table:

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


the first field on the month represents the quantity, the second is cost per kg.

taking into consideration the [end of feb 2012 stock] i need to know at what value is [sales feb 2012].

so i have to make a difference from ending stock starting with the last month, until i reach 0. Then i need to calculated the remaining of the month + the previous months until i reach the value of the quantity sold. The result must be an average cost of the months for the quantity sold.

Manualy the process looks like this :


1455.1 - 1092.3 = 362.8

we still have to check back

362.8 - 985 = -622.2

so this mean that the sales start from 622.2 back , with the cost of 6.9947

1000-622.2 = 377.8 with the cost of 6.9892

the result i need is (622.2*6.9947)+(377.8*6.9892) / 622.2+377.8

this means that the 1000 kg sold were at the average cost of 6.9926


the table contains multiple products, and values can exists in different months.

I apreciate the help and the time given to solve this in advance,

Best regards.
B.
Edited by - tempus on 03/16/2012 03:13:15

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/16/2012 :  04:40:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Will this do?
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

N 56°04'39.26"
E 12°55'05.63"
Edited by - SwePeso on 03/16/2012 04:44:27
Go to Top of Page

tempus
Starting Member

42 Posts
Posted - 03/16/2012 :  11:06:14  Show Profile  Reply with Quote
Dear SwePeso ,

thank you first for the time taken to solve my issue.

For one product with those tables i get the correct result but

i get an infinite loop at this section when i move to my table ( for february 2011 i have 366 products / lines ):

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.[cod produs] = i.[cod produs]
AND t.SeqID = i.SeqID + 1
WHERE i.Quantity > 0


ive let the query to run for 2 and a half hours but with no luck :) .

what i did, was to replace some field in your query with my real table ones.

what could be the issue here?

Thanks in advance,
B.
Go to Top of Page

Peso
Starting Member

1 Posts
Posted - 03/16/2012 :  13:56:58  Show Profile  Reply with Quote
It's probably due to there are some Products which have no more rows to deduct and Quantity is still > 0.
Add " AND @@ROWCOUNT > 0 " to the IF EXISTS clause.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/16/2012 :  15:03:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
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) AND @@ROWCOUNT > 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
--HAVING		COUNT(*) = 2		-- Uncomment this line if you want all products
ORDER BY	Product

-- Clean up
DROP TABLE	#Temp,
		#Iteration


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 03/16/2012 :  15:11:44  Show Profile  Reply with Quote
you again started a new id with name Peso??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/16/2012 :  17:27:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Didn't notice that. That response was from my cell phone.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 03/16/2012 :  17:52:54  Show Profile  Reply with Quote
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"

Oh ok..so that id still exists?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/16/2012 :  18:22:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I thought not, but is does.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

tempus
Starting Member

42 Posts
Posted - 03/20/2012 :  03:58:48  Show Profile  Reply with Quote
Dear SwePeso

the infinite loop has been solved with @@rownumber > 0

but now the result is incorect.

if we take the following example :

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 '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,
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) AND @@ROWCOUNT > 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
--HAVING COUNT(*) = 2 -- Uncomment this line if you want all products
ORDER BY Product

-- Clean up
DROP TABLE #Temp,
#Iteration


the ending stock is 520.2
the sales is 638.2

going back thru the table we see a production of 620.2

ending stock - last production is -100 as the cost of 9.0702

so the sales start from here : 100 kg sold at 9.0702
we need to go back until we cover all the ammount of sales ( 538.2 remaining )
the next production is 413.2 at cost of 9.2947
still remaining 538.2-413.2 = 125 kg
the next one is 646.9 at the cost of 9.235

so the last 125 were sold at 9.235

the average should be = [(100*9.0702)+(413.2*9.2947)+(125*9.235)]/638.2 = 9.24783

but the average now shows = 1.4212 which is incorect.

Im really sorry for the late reply.

thanks in advance for the answer,
Best regards,
B.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/20/2012 :  09:35:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Like this?
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


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

tempus
Starting Member

42 Posts
Posted - 03/23/2012 :  05:29:17  Show Profile  Reply with Quote
Dear SwePeso,

for some reason i only get results for 167 products ( and the result is correct) , but my table contains 366 product ( i only got in the table now products that have quantity sold greater than 0 ) .

i tried to figure out why they dont appear in the select but with no luck.

If you got any kind of sugestion how to verify please tell me.

Best regards,
B.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 03/23/2012 :  05:38:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I can't. If I continue with this, I need access to ALL your data, otherwise everything from my part is a fair guess.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

stepson
Yak Posting Veteran

Romania
85 Posts
Posted - 03/23/2012 :  07:07:50  Show Profile  Reply with Quote
you filter the result when you insert into #temp : WHERE Quantity <> 0

add at end something like ,before Clean up :

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
Edited by - stepson on 03/23/2012 07:08:22
Go to Top of Page

tempus
Starting Member

42 Posts
Posted - 04/03/2012 :  02:44:42  Show Profile  Reply with Quote
i give up.

here's the whole thing:

table creation:


USE [iScalaDB]
GO

/****** Object: Table [dbo].[BT_rmc_uri_reale] Script Date: 04/03/2012 09:33:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BT_rmc_uri_reale](
[cod produs] [nvarchar](35) NOT NULL,
[denumire produs] [nvarchar](25) NOT NULL,
[grupa produs] [nvarchar](10) NOT NULL,
[q ianuarie 2011] [numeric](10, 2) NULL,
[ianuarie 2011] [numeric](10, 4) NULL,
[q februarie 2011] [numeric](10, 2) NULL,
[februarie 2011] [numeric](10, 4) NULL,
[q martie 2011] [numeric](10, 2) NULL,
[martie 2011] [numeric](10, 4) NULL,
[q aprilie 2011] [numeric](10, 2) NULL,
[aprilie 2011] [numeric](10, 4) NULL,
[q mai 2011] [numeric](10, 2) NULL,
[mai 2011] [numeric](10, 4) NULL,
[q iunie 2011] [numeric](10, 2) NULL,
[iunie 2011] [numeric](10, 4) NULL,
[q iulie 2011] [numeric](10, 2) NULL,
[iulie 2011] [numeric](10, 4) NULL,
[q august 2011] [numeric](10, 2) NULL,
[august 2011] [numeric](10, 4) NULL,
[q septembrie 2011] [numeric](10, 2) NULL,
[septembrie 2011] [numeric](10, 4) NULL,
[q octombrie 2011] [numeric](10, 2) NULL,
[octombrie 2011] [numeric](10, 4) NULL,
[q noiembrie 2011] [numeric](10, 2) NULL,
[noiembrie 2011] [numeric](10, 4) NULL,
[q decembrie 2011] [numeric](10, 2) NULL,
[decembrie 2011] [numeric](10, 4) NULL,
[q ianuarie 2012] [numeric](10, 2) NULL,
[ianuarie 2012] [numeric](10, 4) NULL,
[q februarie 2012] [numeric](10, 2) NULL,
[februarie 2012] [numeric](10, 4) NULL,
[q martie 2012] [numeric](10, 2) NULL,
[martie 2012] [numeric](10, 4) NULL,
[q aprilie 2012] [numeric](10, 2) NULL,
[aprilie 2012] [numeric](10, 4) NULL,
[q mai 2012] [numeric](10, 2) NULL,
[mai 2012] [numeric](10, 4) NULL,
[q iunie 2012] [numeric](10, 2) NULL,
[iunie 2012] [numeric](10, 4) NULL,
[q iulie 2012] [numeric](10, 2) NULL,
[iulie 2012] [numeric](10, 4) NULL,
[q august 2012] [numeric](10, 2) NULL,
[august 2012] [numeric](10, 4) NULL,
[q septembrie 2012] [numeric](10, 2) NULL,
[septembrie 2012] [numeric](10, 4) NULL,
[q octombrie 2012] [numeric](10, 2) NULL,
[octombrie 2012] [numeric](10, 4) NULL,
[q noiembrie 2012] [numeric](10, 2) NULL,
[noiembrie 2012] [numeric](10, 4) NULL,
[q decembrie 2012] [numeric](10, 2) NULL,
[decembrie 2012] [numeric](10, 4) NULL,
[q ianuarie 2013] [numeric](10, 2) NULL,
[ianuarie 2013] [numeric](10, 4) NULL,
[sales feb 2012] [numeric](10, 4) NULL,
[end of feb 2012 stock] [numeric](10, 4) NULL
) ON [PRIMARY]

GO

ive inserted the data to an excel which can be found here. It needs to be inserted into the table.

i dont know if im allowed to post a link to an external website but here i go ( if im not allowed please delete) :

http://www.2shared.com/document/0O4CLpn5/data_to_be_inserted_into_table.html


the last script that i use :

/*
Solution by SwePeso
*/

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



-- Populate the staging table
INSERT #Temp
(
[cod produs],
SeqID,
Quantity,
Cost
)
SELECT [cod produs],
ROW_NUMBER() OVER (PARTITION BY [cod produs] ORDER BY thePeriod DESC) - 1 AS SeqID,
Quantity,
Cost
FROM (
SELECT [cod produs], CAST('20110101' AS DATETIME) AS thePeriod, [q ianuarie 2011] AS Quantity, [ianuarie 2011] AS Cost FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110201', [q februarie 2011], [februarie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110301', [q martie 2011], [martie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110401', [q aprilie 2011], [aprilie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110501', [q mai 2011], [mai 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110601', [q iunie 2011], [iunie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110701', [q iulie 2011], [iulie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110801', [q august 2011], [august 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20110901', [q septembrie 2011], [septembrie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20111001', [q octombrie 2011], [octombrie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20111101', [q noiembrie 2011], [noiembrie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20111201', [q decembrie 2011], [decembrie 2011] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20120101', [q ianuarie 2012], [ianuarie 2012] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '20120201', [q februarie 2012], [februarie 2012] FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '99991101', [end of feb 2012 stock], 0 FROM BT_rmc_uri_reale UNION ALL
SELECT [cod produs], '99991201', [sales feb 2012], 0 FROM BT_rmc_uri_reale
) AS d
WHERE Quantity <>0

--select * from BT_rmc_uri_reale order by [cod produs]


DECLARE @SeqID INT
set @SeqID = 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.[cod produs] = t.[cod produs]
AND w.SeqID = 1
INNER JOIN #Temp AS q ON q.[cod produs] = t.[cod produs]
AND q.SeqID = 0
WHERE t.SeqID = 2


-- select * from #Temp

-- 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.[cod produs] = t.[cod produs]
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 [cod produs]
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.[cod produs] = t.[cod produs]
AND w.SeqID = 0
INNER JOIN #Temp AS q ON q.[cod produs] = t.[cod produs]
AND q.SeqID = t.SeqID - 1
WHERE t.Countdown < 0


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




-- Clean up
DROP TABLE #Temp

The results i get is for 167 products. the cost seem to be corect also. I need the result for all the products in the table thou'.


Sincerly many thanks to all the time spent for helping me.

Best wishes,
B.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000