CODEWITHSUNDEEP
sql-serverloopst-sqlcursor
Michael
Michael

Reputation: 2657

T SQL Cursor is only updating row with same value

I'm using MS SQL Server and I have the below table 'tmp_AVG_Weekly_Sales':

RowID   SKU   Shop  Week   Avg_Value   LAMBDA    PMF      Value
  1     ABC   200    2       1          2        0.13     NULL
  2     DEF   250    2       2          4        0.018    NULL
  3     XYZ   300    3       3          6        0.0024   NULL

I need to work out the Value field based on the below logic - I am using a Cursor and Loop:

DECLARE @CUMULATIVE AS FLOAT = 0;
DECLARE @COUNT AS INT = 0;
DECLARE @LAMBDA AS FLOAT;
DECLARE @RowID AS INT;
DECLARE @PoissonCursor AS CURSOR;
DECLARE @THRESHOLD AS FLOAT = 0.99;
DECLARE @PMF AS FLOAT --= EXP(-@LAMBDA)


SET @PoissonCursor = CURSOR FOR

SELECT RowID
FROM 
[tmp_AVG_Weekly_Sales]

OPEN @PoissonCursor;

FETCH NEXT FROM @PoissonCursor INTO @RowID;

WHILE @@FETCH_STATUS = 0

BEGIN

    SELECT @LAMBDA = LAMBDA FROM [tmp_AVG_Weekly_Sales] WHERE RowID = @RowID

    SELECT @PMF = PMF FROM [tmp_AVG_Weekly_Sales] WHERE RowID = @RowID

    WHILE (@CUMULATIVE < @Threshold)

    BEGIN
        SET @CUMULATIVE += @PMF
        SET @COUNT += 1
        SET @PMF = @PMF * (@LAMBDA / @COUNT)

        END

        UPDATE [tmp_AVG_Weekly_Sales] SET [Value] = @COUNT - 1 WHERE RowID = @RowID

    FETCH NEXT FROM @PoissonCursor  INTO @RowID;

END

However, the above is just populating the Value field with the same value:

RowID   SKU   Shop  Week   Avg_Value   LAMBDA    PMF      Value
  1     ABC   200    2       1          2        0.13     6
  2     DEF   250    2       2          4        0.018    6
  3     XYZ   300    3       3          6        0.0024   6

When I am expecting the below:

RowID   SKU   Shop  Week   Avg_Value   LAMBDA    PMF      Value
  1     ABC   200    2       1          2        0.13     6
  2     DEF   250    2       2          4        0.018    9
  3     XYZ   300    3       3          6        0.0024   12

Where am I going wrong?

Upvotes: 0

Views: 231

Answers (1)

gmiley
gmiley

Reputation: 6604

You never reset @CUMULATIVE or increase @Threshold, so the block of SET calls are only executed the first go through and each subsequent UPDATE just uses those original values.

Upvotes: 1

Related Questions