salesprod
asked on
SQL CURSOR Help
Hi. I am trying to create my first cursor that goes through a table row by row calculating a column called MM_CUM. I keep track of the previous row's r1_mmestwireless value as it is used in the next row's calculation for MM_CUM. I try this and it goes through all 15,000 rows and places the same value in the MM_CUM column. Any help with this would be much appreciated. Thank you!
DECLARE @iRowId int,
@r1_mmestwireless money,
@last_mm_cum money,
@mm_cum money
DECLARE MarketModel CURSOR LOCAL
FORWARD_ONLY
FOR
SELECT r1_mmestwireless,mm_cum
FROM mm_mal_test
SET @iRowID = 1
OPEN MarketModel
FETCH NEXT FROM MarketModel
INTO @r1_mmestwireless, @mm_cum
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@iRowID = 1)
BEGIN
UPDATE MM_MAL_TEST
SET mm_cum = @r1_mmestwireless
SET @last_mm_cum = @r1_mmestwireless
END
IF (@iRowID > 1)
BEGIN
UPDATE MM_MAL_TEST
SET mm_cum = @last_mm_cum + @r1_mmestwireless
SET @last_mm_cum = @last_mm_cum + @r1_mmestwireless
END
FETCH NEXT FROM MarketModel
INTO @r1_mmestwireless, @mm_cum
set @iRowID = @iRowID + 1
END
CLOSE MarketModel
DEALLOCATE MarketModel
RETURN
DECLARE @iRowId int,
@r1_mmestwireless money,
@last_mm_cum money,
@mm_cum money
DECLARE MarketModel CURSOR LOCAL
FORWARD_ONLY
FOR
SELECT r1_mmestwireless,mm_cum
FROM mm_mal_test
SET @iRowID = 1
OPEN MarketModel
FETCH NEXT FROM MarketModel
INTO @r1_mmestwireless, @mm_cum
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@iRowID = 1)
BEGIN
UPDATE MM_MAL_TEST
SET mm_cum = @r1_mmestwireless
SET @last_mm_cum = @r1_mmestwireless
END
IF (@iRowID > 1)
BEGIN
UPDATE MM_MAL_TEST
SET mm_cum = @last_mm_cum + @r1_mmestwireless
SET @last_mm_cum = @last_mm_cum + @r1_mmestwireless
END
FETCH NEXT FROM MarketModel
INTO @r1_mmestwireless, @mm_cum
set @iRowID = @iRowID + 1
END
CLOSE MarketModel
DEALLOCATE MarketModel
RETURN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.