TSQL / SQL Calculating Cumulative Total

Calculating a moving cumulative total using TSQL statement.

The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. 

You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

SELECT BusinessEntityID, TerritoryID 

   ,DATEPART(yy,ModifiedDate) AS SalesYear

   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD

   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID 

                                            ORDER BY DATEPART(yy,ModifiedDate) 

                                           ),1) AS MovingAvg

   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 

                                            ORDER BY DATEPART(yy,ModifiedDate) 

                                            ),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5

ORDER BY TerritoryID,SalesYear;

--

Jagadeesan Kandasamy