February 21, 2011 at 1:09 pm
How can I turn the following code into a set operation:
declare @pk INT
select CAST( 0 as int) as pk,
CAST( 0 as int) as product_id,
CAST( 0 as int) as qty_add,
CAST( 0 as int) as qty_start,
CAST( 0 as money) as cost,
CAST( null as date) as created
INTO #temp
delete from #temp
insert into dbo.#temp values
(1,1,100, 0, 1, '12/1/2010'),
(2,1,100, 100, .90, '12/15/2010'),
(3,1,100, 200, .95, '1/11/2011'),
(4,1,100, 300, 1, '1/21/2011'),
(5,1,100, 400, .75, '1/31/2011'),
(6,1,100, 500, .80, '2/1/2011'),
(7,1,100, 600, .90, '2/12/2011'),
(8,1,100, 700, 1, '2/14/2011')
DECLARE cusTest CURSOR FAST_FORWARD READ_ONLY
FOR SELECT pk FROM #temp order by created
Open cusTest
FETCH NEXT FROM cusTest INTO @pk
DECLARE @last_Cost MONEY = 0,
@qty_add int = 0,
@qty_start INT = 0,
@cost MONEY = 0,
@avg_Cost money = 0,
@created DATE
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @qty_add = qty_add, @qty_start = qty_start, @cost = cost, @created = created
from #temp where pk = @pk
SET @avg_cost = ((@qty_start * @avg_Cost) + (@qty_add * @cost)) / (@qty_start + @qty_add)
PRINT @created
PRINT @avg_cost
FETCH NEXT FROM cusTest INTO @pk
Thank you!
February 21, 2011 at 1:12 pm
Mike, I think you might have oversimplified your examples.
I'm assuming the real code doesn't PRINT the results, but does something with them. What would that be?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 21, 2011 at 1:19 pm
Ever notice as soon as you close a window and start looking as something else little things seem to come back to you?
I believe this is what you're looking for to generate a weighted average cost:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
declare @pk INT
select CAST( 0 as int) as pk,
CAST( 0 as int) as product_id,
CAST( 0 as int) as qty_add,
CAST( 0 as int) as qty_start,
CAST( 0 as money) as cost,
CAST( null as datetime) as created
INTO #temp
delete from #temp
insert into dbo.#temp
SELECT 1,1,100, 0, 1, '12/1/2010' UNION ALL
SELECT 2,1,100, 100, .90, '12/15/2010' UNION ALL
SELECT 3,1,100, 200, .95, '1/11/2011' UNION ALL
SELECT 4,1,100, 300, 1, '1/21/2011' UNION ALL
SELECT 5,1,100, 400, .75, '1/31/2011' UNION ALL
SELECT 6,1,100, 500, .80, '2/1/2011' UNION ALL
SELECT 7,1,100, 600, .90, '2/12/2011' UNION ALL
SELECT 8,1,100, 700, 1, '2/14/2011'
--calculation of a weighted cost.
SELECT
product_id,
SUM( qty_add * cost) / SUM( qty_add) AS weightedCost
FROM
#temp
group by
product_id
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 21, 2011 at 1:28 pm
Craig,
You are correct. The real function would not print the average cost. It will be turned into a user defined function that would return the weighted cost. Thanks for your help.
Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply