Set-Based vs. Cursor-Based Solutions for Running Aggregates

DECLARE @Result AS TABLE

(

  empid  INT, 

  dt     DATETIME, 

  qty    INT, 

  val    MONEY, 

  sumqty BIGINT, 

  avgqty NUMERIC(12, 2), 

  sumval MONEY, 

  avgval MONEY

); 


DECLARE

  @empid    AS INT, 

  @prvempid AS INT, 

  @dt       AS DATETIME, 

  @qty      AS INT, 

  @val      AS MONEY, 

  @sumqty   AS BIGINT, 

  @sumval   AS MONEY, 

  @count    AS INT; 


DECLARE C CURSOR FAST_FORWARD FOR

  SELECT empid, dt, qty, val

  FROM dbo.Sales

  ORDER BY empid, dt; 


OPEN C


FETCH NEXT FROM C INTO @empid, @dt, @qty, @val; 


SELECT @prvempid = @empid, @sumqty = 0, @sumval = 0, @count = 0; 


WHILE @@fetch_status = 0

BEGIN

  IF @empid  @prvempid

    SELECT @prvempid = @empid, @sumqty = 0, @sumval = 0, @count = 0; 


  SELECT

    @sumqty = @sumqty + @qty, 

    @sumval = @sumval + @val, 

    @count  = @count  + 1; 


  INSERT INTO @Result(empid, dt, qty, val, sumqty, avgqty, sumval, avgval) 

    VALUES(@empid, @dt, @qty, @val, 

      @sumqty, 1.*@sumqty / @count, @sumval, @sumval / @count); 


  FETCH NEXT FROM C INTO @empid, @dt, @qty, @val; 

END


CLOSE C; 


DEALLOCATE C; 


SELECT * FROM @Result;