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 generate running totals with a simple update?

Execute the following SQL Server T-SQL script in SSMS Query Editor to demonstrate running total generation on the Total Due amount using multiple-value assignment in UPDATE. There is an ongoing debate in database circles about the validity of such use.

USE AdventureWorks;

-- SQL running total - SQL multiple value assignment update

DECLARE  @RunningTotal MONEY

DECLARE  @Result  TABLE(

                        SalesOrderID INT    NOT NULL    PRIMARY KEY,

                        TotalDue     MONEY,

                        RunningTotal MONEY

                        )

 

SET @RunningTotal = 0.0

 

INSERT INTO @Result

           (SalesOrderID,

            TotalDue)

SELECT   SalesOrderID,

         TotalDue

FROM     Sales.SalesOrderHeader

ORDER BY SalesOrderID

 

UPDATE @Result

SET    @RunningTotal = RunningTotal = @RunningTotal + TotalDue

-- SQL currency formatting

SELECT   SalesOrderId,

         [Total Due] = '$' + convert(VARCHAR,TotalDue,1),

         [Running Total] = '$' + convert(VARCHAR,RunningTotal,1)

FROM     @Result

ORDER BY SalesOrderID

 

GO

/*

Partial results

 

SalesOrderId      Total Due   Running Total

43659             $23,153.23  $23,153.23

43660             $1,457.33   $24,610.56

43661             $36,865.80  $61,476.36

43662             $32,474.93  $93,951.30

43663             $472.31     $94,423.61

43664             $27,510.41  $121,934.02

43665             $16,158.70  $138,092.71

*/

 

 

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