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