DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to architect running total using cursor?
Contributed by Paul Nielsen, www.sqlserverbible.com

Execute the following T-SQL script in Query Editor to demonstrate the usage of cursor for running total generation.

Generally this solution performs better than applying correlated subquery.

USE AdventureWorks

 

ALTER TABLE Sales.SalesOrderHeader

  ADD CumulativeTotal MONEY NOT NULL

  CONSTRAINT dfSalesOrderHeader DEFAULT(0)

go --

 

 

 

SET NoCount ON

DECLARE

  @SalesOrderID INT,

  @TotalDue MONEY,

  @CumulativeTotal MONEY

 

SET @CumulativeTotal = 0

  -- 1) set up the cursor     

  DECLARE cRun CURSOR FAST_FORWARD

    FOR

      SELECT SalesOrderID, TotalDue

        FROM Sales.SalesOrderHeader

        ORDER BY OrderDate, SalesOrderID

 

   -- 2) open the cursor

  OPEN cRun

 

  FETCH cRun INTO @SalesOrderID, @TotalDue  -- prime the cursor

  WHILE @@Fetch_Status = 0

    BEGIN

      SET @CumulativeTotal = @CumulativeTotal + @TotalDue

      UPDATE Sales.SalesOrderHeader

        SET CumulativeTotal = @CumulativeTotal

        WHERE SalesOrderID = @SalesOrderID   

      FETCH cRun INTO @SalesOrderID, @TotalDue  -- fetch next

    END

  CLOSE cRun

 

  DEALLOCATE cRun

go --

 

 

SELECT SalesOrderID, TotalDue, CumulativeTotal

  FROM Sales.SalesOrderHeader

  ORDER BY OrderDate, SalesOrderID

go --

 

ALTER TABLE Sales.SalesOrderHeader

  DROP CONSTRAINT dfSalesOrderHeader

 

ALTER TABLE Sales.SalesOrderHeader

  DROP COLUMN CumulativeTotal

 

 


Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE