Inventory average cost - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inventory average cost

Author

Topic

deep2002
Starting Member

5 Posts

Posted - 2009-08-10 : 21:45:43

I have been looking through every site to find a solution to this problem. I am trying to get the average cost for the following series of transactions, I have put what the average cost should be after each transaction for reference:

Date Quantity Price TotalQuantity AverageCost
Dec 3 2008 5,000 4.28, 5,000 4.28
Dec 3 2008 5,000 4.60 10,000 4.44
Jan 7 2009 5,000 7.74 15,000 5.49
Jan 12 2009 -5,000 6.52, 10,000 5.49
Jan 19 2009 7,000 8.00 17,000 6.52

I can't use the SUM function because the aggregate function does not account for order. For these the order does matter. I am trying to set something where the initial average cost is 0 and average costs changes only when something is bought. If something is sold it should not affect the average costs, when selling only the net quantity is affected and only buying changes the average cost. I am trying to make a loop for Average cost for each row as:
When quantity bought:
((Previous row net quantity * previous row average cost) + (this row quantity * Price))/(previous net quantity + this row quantity)

When quantity sold:
((previous row net quantity * previous row average cost) - (this row quantity * previous row average cost)) / (previous row quantity - this row quantity)

If anyone know how to do this you will be a sql guru in my books. I have not found anything that shows how to solve this on the web yet.

*edit: bold should be 7.74 not 7.75

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-10 : 23:55:59

[code]
DECLARE @sample TABLE
(
[Date] datetime,
Quantity int,
Price decimal(10,2),
TotalQuantity int,
AverageCost decimal(10,2)
)
INSERT INTO @sample
SELECT '20081203', 5000, 4.28, 5000, 4.28 UNION ALL
SELECT '20081203', 5000, 4.60, 10000, 4.44 UNION ALL
SELECT '20090107', 5000, 7.75, 15000, 5.49 UNION ALL
SELECT '20090112', -5000, 6.52, 10000, 5.49 UNION ALL
SELECT '20090119', 7000, 8.00, 17000, 6.52

;WITH data ([Date], Quantity, Price, TotalQuantity, AverageCost, row_no)
AS
(
SELECT [Date], Quantity, Price, TotalQuantity, AverageCost,
row_no = row_number() OVER (ORDER BY [Date])
FROM @sample
)
SELECT d.[Date], d.Quantity, d.Price, d.TotalQuantity, d.AverageCost, a.AvgCost
FROM data d
CROSS APPLY
(
SELECT SUM(x.Quantity * x.Price) / d.TotalQuantity AS AvgCost
FROM data x
WHERE x.row_no <= d.row_no
) a
/*
Date Quantity Price TotalQuantity AverageCost AvgCost
----------- ----------- ------------ ------------- ------------ ----------
2008-12-03 5000 4.28 5000 4.28 4.280000
2008-12-03 5000 4.60 10000 4.44 4.440000
2009-01-07 5000 7.75 15000 5.49 5.543333
2009-01-12 -5000 6.52 10000 5.49 5.055000
2009-01-19 7000 8.00 17000 6.52 6.267647

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-11 : 01:48:05

>"<??? i seem to be can't understand how sifu yours query can meet with his condition of bought...

Hope can help...but advise to wait pros with confirmation...

deep2002
Starting Member

5 Posts

Posted - 2009-08-11 : 09:21:42

quote:

Originally posted by khtan


DECLARE @sample TABLE
(	
	[Date] 		datetime,
	Quantity 	int,
	Price 		decimal(10,2),
	TotalQuantity 	int,
	AverageCost	decimal(10,2)
)
INSERT INTO @sample
SELECT	'20081203',  5000, 4.28,  5000, 4.28	UNION ALL
SELECT	'20081203',  5000, 4.60, 10000, 4.44	UNION ALL
SELECT	'20090107',  5000, 7.75, 15000, 5.49	UNION ALL
SELECT	'20090112', -5000, 6.52, 10000, 5.49	UNION ALL
SELECT	'20090119',  7000, 8.00, 17000, 6.52

;WITH data ([Date], Quantity, Price, TotalQuantity, AverageCost, row_no)
AS
(
	SELECT	[Date], Quantity, Price, TotalQuantity, AverageCost, 
		row_no	= row_number() OVER (ORDER BY [Date])
	FROM	@sample
)
SELECT	d.[Date], d.Quantity, d.Price, d.TotalQuantity, d.AverageCost, a.AvgCost
FROM	data d
	CROSS APPLY
	(
		SELECT	SUM(x.Quantity * x.Price) / d.TotalQuantity AS AvgCost
		FROM	data x
		WHERE	x.row_no	<= d.row_no
	) a
/*
Date        Quantity    Price        TotalQuantity AverageCost  AvgCost   
----------- ----------- ------------ ------------- ------------ ----------
2008-12-03  5000        4.28         5000          4.28         4.280000
2008-12-03  5000        4.60         10000         4.44         4.440000
2009-01-07  5000        7.75         15000         5.49         5.543333
2009-01-12  -5000       6.52         10000         5.49         5.055000
2009-01-19  7000        8.00         17000         6.52         6.267647

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Thank you for the reply. The problem in the output is that the average costs are correct until a sell quantity is entered, the average cost changes which it shouldn't.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-11 : 10:13:18

[code]
DECLARE @sample TABLE
(
ID int identity,
[Date] datetime,
Quantity int,
Price decimal(10,2),
TotalQuantity int,
AverageCost decimal(10,2),
AvgCost decimal(10,2)
)
INSERT INTO @sample ([Date], Quantity, Price, TotalQuantity, AverageCost)
SELECT '20081203', 5000, 4.28, 5000, 4.28 UNION ALL
SELECT '20081203', 5000, 4.60, 10000, 4.44 UNION ALL
SELECT '20090107', 5000, 7.75, 15000, 5.49 UNION ALL
SELECT '20090112', -5000, 6.52, 10000, 5.49 UNION ALL
SELECT '20090119', 7000, 8.00, 17000, 6.52

; WITH data ([Date], Quantity, Price, TotalQuantity, AverageCost, AvgCost, ID)
AS
(
-- Anchor Member
SELECT [Date], Quantity, Price, TotalQuantity, AverageCost, AvgCost = Price, ID
FROM @sample
WHERE ID = 1

UNION ALL

-- Recursive Member
SELECT s.[Date], s.Quantity, s.Price, s.TotalQuantity, s.AverageCost,
AvgCost = CASE WHEN s.Quantity > 0
THEN CONVERT(decimal(10,2), ((d.TotalQuantity * d.AvgCost) + (s.Quantity * s.Price)) / (d.TotalQuantity + s.Quantity))
ELSE d.AvgCost
END,
ID = d.ID + 1
FROM @sample s
INNER JOIN data d ON s.ID = d.ID + 1
)
SELECT [Date], Quantity, Price, TotalQuantity, AverageCost, AvgCost
FROM data

/*
Date Quantity Price TotalQuantity AverageCost AvgCost
----------- ----------- ------------ ------------- ------------ ------------
2008-12-03 5000 4.28 5000 4.28 4.28
2008-12-03 5000 4.60 10000 4.44 4.44
2009-01-07 5000 7.75 15000 5.49 5.54
2009-01-12 -5000 6.52 10000 5.49 5.54
2009-01-19 7000 8.00 17000 6.52 6.55

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

deep2002
Starting Member

5 Posts

Posted - 2009-08-12 : 15:10:47

Khtan...thank you thank you thank you thank you thank you very much!

I'm still testing it but so far its working perfectly!

You have no idea how much you helped me. If there was a thanks button or a score button I would be hitting it for you till my hand lost feelings!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-12 : 22:22:03

wow nice 1 sifu!!!

Hope can help...but advise to wait pros with confirmation...

PeterP
Starting Member

2 Posts

Posted - 2009-08-25 : 01:36:53

quote:

Originally posted by khtan


DECLARE @sample TABLE
(	
	ID		int identity,
	[Date] 		datetime,
	Quantity 	int,
	Price 		decimal(10,2),
	TotalQuantity 	int,
	AverageCost	decimal(10,2),
	AvgCost		decimal(10,2)
)
INSERT INTO @sample ([Date], Quantity, Price, TotalQuantity, AverageCost)
SELECT	'20081203',  5000, 4.28,  5000, 4.28	UNION ALL
SELECT	'20081203',  5000, 4.60, 10000, 4.44	UNION ALL
SELECT	'20090107',  5000, 7.75, 15000, 5.49	UNION ALL
SELECT	'20090112', -5000, 6.52, 10000, 5.49	UNION ALL
SELECT	'20090119',  7000, 8.00, 17000, 6.52

; WITH data ([Date], Quantity, Price, TotalQuantity, AverageCost, AvgCost, ID)
AS
(
	-- Anchor Member
	SELECT	[Date], Quantity, Price, TotalQuantity, AverageCost, AvgCost = Price, ID
	FROM	@sample
	WHERE	ID	= 1
	
	UNION ALL
	
	-- Recursive Member
	SELECT	s.[Date], s.Quantity, s.Price, s.TotalQuantity, s.AverageCost, 
		AvgCost	= CASE 	WHEN s.Quantity > 0 
				THEN CONVERT(decimal(10,2), ((d.TotalQuantity * d.AvgCost) + (s.Quantity * s.Price)) / (d.TotalQuantity + s.Quantity))
				ELSE d.AvgCost
				END,
		ID 	= d.ID + 1
	FROM	@sample s
		INNER JOIN data d	ON	s.ID	= d.ID + 1
)
SELECT	[Date], Quantity, Price, TotalQuantity, AverageCost, AvgCost
FROM	data

/*
Date        Quantity    Price        TotalQuantity AverageCost  AvgCost      
----------- ----------- ------------ ------------- ------------ ------------ 
2008-12-03  5000        4.28         5000          4.28         4.28
2008-12-03  5000        4.60         10000         4.44         4.44
2009-01-07  5000        7.75         15000         5.49         5.54
2009-01-12  -5000       6.52         10000         5.49         5.54
2009-01-19  7000        8.00         17000         6.52         6.55

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Hi khtan

Thank you for your code.
Unfortunately, I tested with more than 100 records and I get an error.
Any suggestions?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-25 : 01:44:48

what's the error ?

Can you post the data that generate the error ?

KH
[spoiler]Time is always against us[/spoiler]

PeterP
Starting Member

2 Posts

Posted - 2009-08-25 : 04:18:06

quote:
Originally posted by khtan

what's the error ?

Can you post the data that generate the error ?

KH
[spoiler]Time is always against us[/spoiler]

Hi khtan

Thank you for the quick response.

I add abot 200 recotds (compy and paste first 4 select)

INSERT INTO @sample ([Date], Quantity, Price, TotalQuantity, AverageCost)
SELECT '20081203', 5000, 4.28, 5000, 4.28 UNION ALL
SELECT '20081203', 5000, 4.60, 10000, 4.44 UNION ALL
SELECT '20090107', 5000, 7.75, 15000, 5.49 UNION ALL
SELECT '20090112', -5000, 6.52, 10000, 5.49 UNION ALL
...
...
...
SELECT '20090119', 7000, 8.00, 17000, 6.52

And I get an error:
(200 row(s) affected)
Msg 530, Level 16, State 1, Line 213
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

deep2002
Starting Member

5 Posts

Posted - 2009-08-25 : 11:29:10

Your getting that error because recursive CTE have a default maximum loop of 100 in order to prevent an infinite loop from happening. You can remove the default loop limit by adding:
OPTION (MAXRECURSION 0) to the end of the function

Becareful because then you risk your code going into an infinite loop.

levin
Starting Member

4 Posts

Posted - 2009-08-25 : 13:20:35

Very lucky. i looking exactly for this solution for my stock control software. But i have difficultly apply to ms access query. I know this site is ms sql, but i really need to apply in ms access query. can you all guru please help...

Levin

levin
Starting Member

4 Posts

Posted - 2009-08-27 : 10:12:24

No idea on this topic??? Convert this function into Ms Access... I need this urgently.

Levin

levin
Starting Member

4 Posts

Posted - 2009-09-02 : 09:25:53

Anyone help covert this function into ms access query...

Levin

levin
Starting Member

4 Posts

Posted - 2009-09-04 : 08:06:13

is this possible... to do this in ms access query...???

Levin