Click here to Skip to main content
15,890,527 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table, and i declare a cursor to get average price from the table,but cursor does not work correctly , please help me

Table
id      CurentSock  UnitPrice
16	20.00	    0.25
16	120.00	    0.32
16	30.00	    0.35

Cursor:
SQL
declare @count int
declare @sum decimal(18,2)
declare @currentStock decimal(18, 2)
declare @result decimal(18,2)
declare @itemID int
declare @unitPrice decimal(18,2)
declare @totalUnitPrice decimal(18,2)
declare @avgUnitPrice decimal(18,2)
declare @totalOrder int

set @count = 0
set @sum = 0
set @currentStock = 0
set @unitPrice = 0
set @totalOrder = 120
set @totalUnitPrice =0

declare FIFOAVG cursor local fast_forward for
SELECT 
	 [ItemId]
      ,[CurrentStock]
      ,[UnitPrice]
    
  FROM [Nimble].[dbo].[Proc_ReqSlave]
   where  [CurrentStock] <> 0
   AND
	[ItemId]=16
 
  open FIFOAVG
  
  fetch next from FIFOAVG into @itemID,@currentStock,@unitPrice 
 
  while @@FETCH_STATUS = 0
  begin
  
  IF(@totalOrder > @sum)
 
  BEGIN
	  set @sum = @sum + @CurrentStock
	  set @count = @count + 1
	  set @totalUnitPrice = @totalUnitPrice + @unitPrice
  END
  	
  fetch next from FIFOAVG into @itemID,@currentStock,@unitPrice  
  end
  
  set @result = @sum/@count
  set @avgUnitPrice = @totalUnitPrice / @count
  
  close FIFOAVG
  deallocate FIFOAVG
  select @itemID
   select @totalOrder as 'Total Order'
  select @sum as 'Sum'
  select @count as 'Count'
  select @totalUnitPrice 'Total Unit Price'
  select @avgUnitPrice as 'avg'

Cursor result:

Total Order 120
sum 140
count 2
total price 0.57
average 0.29


Result will be
sum 170
count 3
total price 0.92 and 
average 0.31

what's wrong with cursor please help me.
Posted
Updated 5-Jun-13 23:42pm
v2

1 solution

Try this (no cursor):
SQL
DECLARE @tbl TABLE(id INT, [CurentStock] DECIMAL(8,2),  UnitPrice DECIMAL(8,2))

INSERT INTO @tbl (id, [CurentStock], UnitPrice)
SELECT 16 AS id, 20.00 AS [CurentStock], 0.25 AS UnitPrice
UNION ALL SELECT 16, 120.00, 0.32
UNION ALL SELECT 16, 30.00, 0.35

SELECT 'total price: ' AS Descript, SUM(UnitPrice) AS [Value]
FROM @tbl
UNION ALL
SELECT 'count: ' AS Descript, COUNT(id) AS [Value]
FROM @tbl
UNION ALL
SELECT 'sum: ' AS Descript, SUM([CurentStock]) AS [Value]
FROM @tbl
UNION ALL
SELECT 'average: ' AS Descript, AVG([UnitPrice]) AS [Value]
FROM @tbl


Result:
total price:    0.92
count:          3.00
sum:            170.00
average:        0.31
 
Share this answer
 
Comments
Md. Shihab Uddin 6-Jun-13 6:02am    
Thanks,
But, What happened with unlimited data
Maciej Los 6-Jun-13 6:07am    
What you mean: for unlimited data?
You'll see ;)
Raja Sekhar S 7-Jun-13 5:38am    
The Query Which Maciej Los Wrote Will Work Much more faster than Cursor... It's Better to avoid Cursors When ever possible... B'coz Cursors Consume more Time and Resources... While learning i was Doing the Same Mistake... Still doing it some Times.....

Voted +5
Maciej Los 7-Jun-13 6:37am    
Thank you, Raja ;)
In Poland we have a sentence: "A man is studying all life and die stupid" ;)
Raja Sekhar S 7-Jun-13 6:55am    
Hoping not to Die Stupid....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900