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 generate RUNNING TOTAL with FAST FORWARD cursor?

Execute the following Microsoft T-SQL example script in SQL Server Management Studio Query Editor to generate RUNNING TOTAL for June, 2001 sales orders.

------------
-- Microsoft T-SQL running total generation with cursor (while loop)
------------
USE AdventureWorks;
-- MSSQL cursor declaration
DECLARE curRunningTotal CURSOR LOCAL FAST_FORWARD FOR
SELECT   SalesOrderId,
         OrderDate = convert(CHAR(10),OrderDate,111),
         TotalDue
FROM     Sales.SalesOrderHeader soh
WHERE    year(OrderDate) = 2001
         AND month(OrderDate) = 7
ORDER BY OrderDate;
 
OPEN curRunningTotal
DECLARE  @SalesOrderID INT
DECLARE  @OrderDate CHAR(10)
DECLARE  @TotalDue MONEY
DECLARE  @RunningTotal MONEY
SET @RunningTotal = 0
-- T-SQL local table variable with running total column
DECLARE  @Report  TABLE(
                        SalesOrderID INT    NOT NULL    PRIMARY KEY,
                        OrderDate    CHAR(10),
                        TotalDue     MONEY,
                        RunningTotal MONEY
                        )
 
FETCH NEXT FROM curRunningTotal
INTO @SalesOrderID,
     @OrderDate,
     @TotalDue
-- T-SQL while loop
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @RunningTotal = @RunningTotal + @TotalDue
    
    INSERT @Report
    VALUES(@SalesOrderID,@OrderDate,@TotalDue,@RunningTotal)
    
    FETCH NEXT FROM curRunningTotal
    INTO @SalesOrderID,
         @OrderDate,
         @TotalDue
  END -- while
CLOSE curRunningTotal
DEALLOCATE curRunningTotal
 
-- SQL select results from table variable
SELECT   *
FROM     @Report
ORDER BY SalesOrderID
GO
/* Partial results
 
SalesOrderID      OrderDate   TotalDue    RunningTotal
43659             2001/07/01  27231.5495  27231.5495
43660             2001/07/01  1716.1794   28947.7289
43661             2001/07/01  43561.4424  72509.1713
43662             2001/07/01  38331.9613  110841.1326
43663             2001/07/01  556.2026    111397.3352
43664             2001/07/01  32390.2031  143787.5383
43665             2001/07/01  19005.2087  162792.747

*/

------------

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