Need Help with SP please..

[Pete]

Hi I am a newbie to SP's cursurs and all that stuff I have a web application that will access this data and the table gets refresehed with data each morning but I need to recalculate some values.

CREATE PROCEDURE recalc8 AS
DECLARE @xVar INT
DECLARE @xVariance INT
DECLARE @WeekEndingValue datetime
DECLARE @xSize nvarchar

SET @xvar = 0

DECLARE yourCURSOR CURSOR FOR
SELECT variance,weekending,[size] FROM tbldata WHERE [size] = '8'
OPEN yourCURSOR
FETCH NEXT FROM yourCURSOR
INTO @xVariance, @WeekEndingValue, @xSize
WHILE @@FETCH_STATUS = 0
BEGIN
SET @xVar = @xVar + @xVariance
UPDATE tblData
SET cumvar = @xvar
WHERE weekending = @WeekEndingValue
AND [size] = @xSize
FETCH NEXT FROM yourCURSOR
END
CLOSE yourCURSOR
DEALLOCATE yourCURSOR

The problem I am having is:

1.It is just updating the first record.
2.It summing everything into the first record when it does update.

example of table what this quesry is doing now:

WeekEnding Size Effic DailyCap WorkDays PlanUtil Demand Variance cumvar
----------- ---- ----- -------- -------- -------- ------ -------- ------
2002-05-24 8 70 81 5 0 423 -423 -5922
2002-05-31 8 70 81 5 0 143 -143 0
2002-06-07 8 70 81 5 0 435 -435 0

what we are looking for is this:

WeekEnding Size Effic DailyCap WorkDays PlanUtil Demand Variance cumvar
----------- ---- ----- -------- -------- -------- ------ -------- ------
2002-05-24 8 70 81 5 0 423 -423 -423
2002-05-31 8 70 81 5 0 143 -143 -566
2002-06-07 8 70 81 5 0 435 -435 -1001


Thanks.

[John]

Pete, just looking quick, you selecting into variables in the first FETCH NEXT statement, and when you FETCH NEXT the second time, you did not get new vaules into your variables. The FETCH NEXT INTO @xVar.. basicaly needs repeated for each row fetched.
-John


------------
Pete at 5/23/2002 3:31:32 PM


Hi I am a newbie to SP's cursurs and all that stuff I have a web application that will access this data and the table gets refresehed with data each morning but I need to recalculate some values.

CREATE PROCEDURE recalc8 AS
DECLARE @xVar INT
DECLARE @xVariance INT
DECLARE @WeekEndingValue datetime
DECLARE @xSize nvarchar

SET @xvar = 0

DECLARE yourCURSOR CURSOR FOR
SELECT variance,weekending,[size] FROM tbldata WHERE [size] = '8'
OPEN yourCURSOR
FETCH NEXT FROM yourCURSOR
INTO @xVariance, @WeekEndingValue, @xSize
WHILE @@FETCH_STATUS = 0
BEGIN
SET @xVar = @xVar + @xVariance
UPDATE tblData
SET cumvar = @xvar
WHERE weekending = @WeekEndingValue
AND [size] = @xSize
FETCH NEXT FROM yourCURSOR
END
CLOSE yourCURSOR
DEALLOCATE yourCURSOR

The problem I am having is:

1.It is just updating the first record.
2.It summing everything into the first record when it does update.

example of table what this quesry is doing now:

WeekEnding Size Effic DailyCap WorkDays PlanUtil Demand Variance cumvar
----------- ---- ----- -------- -------- -------- ------ -------- ------
2002-05-24 8 70 81 5 0 423 -423 -5922
2002-05-31 8 70 81 5 0 143 -143 0
2002-06-07 8 70 81 5 0 435 -435 0

what we are looking for is this:

WeekEnding Size Effic DailyCap WorkDays PlanUtil Demand Variance cumvar
----------- ---- ----- -------- -------- -------- ------ -------- ------
2002-05-24 8 70 81 5 0 423 -423 -423
2002-05-31 8 70 81 5 0 143 -143 -566
2002-06-07 8 70 81 5 0 435 -435 -1001


Thanks.