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