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

Execute the following T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the generation of a Running Total columns.

Running total calculation applying CTE and ROW_NUMBER() function for row identification and sequencing.

 

------------

-- SQL Server Running Total Calculation

------------

DECLARE @DateStart date='2004-01-01'

;WITH CTE

     AS (SELECT ID = ROW_NUMBER() OVER(ORDER BY PurchaseOrderID),

                PurchaseOrderID, OrderDate = CONVERT(DATE,OrderDate),SubTotal

         FROM   AdventureWorks2008.Purchasing.PurchaseOrderHeader

         WHERE  OrderDate >= @DateStart)

SELECT   PurchaseOrderID,  OrderDate, SubTotal,

         RunningTotal = (SELECT SUM(SubTotal)

                         FROM   CTE

                         WHERE  ID <= A.ID)

FROM     CTE AS A

ORDER BY ID

/*

PurchaseOrderID   OrderDate   SubTotal    RunningTotal

1313              2004-01-03  91117.95    91117.95

1314              2004-01-03  525.00      91642.95

1315              2004-01-03  2003.6835   93646.6335

1316              2004-01-03  26455.275   120101.9085

1317              2004-01-03  944.37      121046.2785

.....

*/

------------

Running total on the TotalDue column of SalesOrderHeader table in AdventureWorks2008 sample database.
 
USE AdventureWorks2008;
GO

DECLARE @Year INT = 2001, @Month INT = 7

SELECT   SequenceNo = ROW_NUMBER()

                        OVER(ORDER BY OrderDate, SalesOrderID),

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

         SalesOrderId, -- SQL dollar formatting - money / currency format 

         TotalDue = '$' + convert(VARCHAR,TotalDue,1),

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

         (SELECT sum(TotalDue)

          FROM   Sales.SalesOrderHeader

          WHERE  SalesOrderID <= soh.SalesOrderID

               AND year(OrderDate) = @Year

               AND month(OrderDate) = @Month),

                                         1)

FROM     Sales.SalesOrderHeader soh

WHERE    year(OrderDate) = @Year

         AND month(OrderDate) = @Month

ORDER BY SequenceNo;

GO

/*

SequenceNo  OrderDate   SalesOrderId      TotalDue    Running Total

1           2001/07/01  43659             $27,231.55  $27,231.55

2           2001/07/01  43660             $1,716.18   $28,947.73

3           2001/07/01  43661             $43,561.44  $72,509.17

4           2001/07/01  43662             $38,331.96  $110,841.13

5           2001/07/01  43663             $556.20     $111,397.34

6           2001/07/01  43664             $32,390.20  $143,787.54

7           2001/07/01  43665             $19,005.21  $162,792.75

.....*/


---------


Running total on the Freight charges column of Orders table in Northwind sample database.
 
 
-- SQL running total - SQL Server running total column - SQL correlated subquery
USE Northwind;
GO
 
SELECT   o1.OrderID,
         o1.ShipName,
         o1.ShipCity,
         o1.Freight,
         OrderDate = convert(CHAR(10),OrderDate,111),
         [Running Total on Freight] =
         (SELECT '$' + convert(VARCHAR,sum(o2.Freight),1)
                     FROM   Orders o2
                     WHERE  o2.OrderID <= o1.OrderID
                     AND o2.ShipCountry = 'Canada')
FROM     Orders o1
WHERE    o1.ShipCountry = 'Canada'
ORDER BY o1.OrderID
GO
/* Partial results
OrderID     ShipName          ShipCity    Freight     OrderDate   Running Total on Freight
10332       Mère Paillarde    Montréal    52.84       1996/10/17  $52.84
10339       Mère Paillarde    Montréal    15.66       1996/10/28  $68.50
10376       Mère Paillarde    Montréal    20.39       1996/12/09  $88.89
10389       Bottom-Dollar     Tsawassen   47.42       1996/12/20  $136.31
10410       Bottom-Dollar     Tsawassen   2.40        1997/01/10  $138.71

*/

Related links:

Calculating Running Totals in SQL Server 2005, The optimal solution?

http://www.sqlusa.com/bestpractices2005/runningtotalusingcursor/

 

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