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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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