Listing 2: Nested Iterations, Using Loops
SELECT ROW_NUMBER() OVER(PARTITION BY empid ORDER BY dt) AS rownum,
empid, dt, qty, CAST(qty AS BIGINT) AS sumqty
INTO #Sales
FROM dbo.Sales;
CREATE UNIQUE CLUSTERED INDEX idx_rownum_empid ON #Sales(rownum, empid);
DECLARE @rownum AS INT;
SET @rownum = 1;
BEGIN TRAN;
WHILE 1 = 1
BEGIN
SET @rownum = @rownum + 1;
UPDATE CUR
SET sumqty = PRV.sumqty + CUR.qty
FROM #Sales AS CUR
JOIN #Sales AS PRV
ON CUR.rownum = @rownum
AND PRV.rownum = @rownum - 1
AND CUR.empid = PRV.empid;
IF @@rowcount = 0 BREAK;
END
COMMIT TRAN;
SELECT empid, dt, qty, sumqty
FROM #Sales;
DROP TABLE #Sales;