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 a RUNNING TOTAL with CROSS APPLY?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to generate running total for the sales order amount.

-- SQL Running Total generation - sql running total column - sql server running total

-- T SQL running total - sql calculate running total - transact sql running total

-- T SQL  cross apply - cross apply table valued function - cross apply subselect

USE AdventureWorks2008;

 

DECLARE @OrderYear int = 2001, @OrderMonth int = 7

 

SELECT   SalesOrderId,

         OrderDate = convert(CHAR(10),OrderDate,111),

         TotalDue = '$'+CONVERT(varchar,TotalDue,1),

         [Running Total] = '$'+CONVERT(varchar,[Running Total],1)

FROM     Sales.SalesOrderHeader soh

         CROSS APPLY (SELECT [Running Total] = sum(TotalDue)

                      FROM   Sales.SalesOrderHeader

                      WHERE  SalesOrderID <= soh.SalesOrderID

                             AND year(OrderDate) = @OrderYear

                             AND month(OrderDate) = @OrderMonth) AS RT

WHERE    year(OrderDate) = @OrderYear

         AND month(OrderDate) = @OrderMonth

ORDER BY SalesOrderID;

 

/* Partial results

 

SalesOrderId      OrderDate   TotalDue    Running Total

43691             2001/07/01  $1,159.98   $490,808.34

43692             2001/07/01  $51,056.50  $541,864.84

43693             2001/07/01  $27,204.50  $569,069.34

43694             2001/07/01  $27,325.62  $596,394.96

43695             2001/07/01  $52,314.34  $648,709.30

43696             2001/07/01  $556.20     $649,265.50

43697             2001/07/01  $3,953.99   $653,219.49

*/

 

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