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
*/
------------ |
|
|
|
|
|
|
|
|