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