T SQL Cursor is only updating row with same value

Question

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?

Are you sure that your calculation is correct? If I run the code, it goes on an endless loop because @PMF reaches 0 before reaching the Treshold. Also, the answer from gmiley shows why you're getting the same results for all rows. — Luis Cazares
– Luis Cazares, Commented May 28, 2019 at 12:50

gmiley · Accepted Answer · 2019-05-28 12:32:12Z

1

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.

answered May 28, 2019 at 12:32

gmiley

6,6041 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael Over a year ago

@Threshold is set at 0.99

gmiley Over a year ago

Yes, but @CUMULATIVE builds up to that (or more) on the first main iteration. You never reset @CUMULATIVE to 0, or increase @THRESHOLD to a higher number. So after the first iteration @CUMULATIVE is always greater than @THREASHOLD so the inner loop never executes.

Luis Cazares Over a year ago

@COUNT also needs to be reset.

gmiley Over a year ago

@LuisCazares No, he wants a running total on @COUNT. (At least that was my understanding, if not, then yes that should be reset too.)

Michael Over a year ago

so I need to set @CUMULATIVE\COUNT to 0 after the update?

|

Collectives™ on Stack Overflow

T SQL Cursor is only updating row with same value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related