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 calculate 12-month moving total?

Execute the following Microsoft SQL Server 2008 T-SQL database script in Query Editor to demonstrate the use of the T-SQL CTE for moving total calculation.

USE AdventureWorks2008;

-- SQL Server 2008 T-SQL code - CTE: Common Table Expression - nested CTE

-- SQL 12-Month Moving Total - Moving 12-Month Total Calculation

WITH CTE

     AS (SELECT   [Month] = CONVERT(DATE,CONVERT(VARCHAR,YEAR(OrderDate)) +

                      '/' + CONVERT(VARCHAR,MONTH(OrderDate)) + '/01'),

                  TotMoSales = SUM(TotalDue)

         FROM     Sales.SalesOrderHeader

         GROUP BY CONVERT(DATE,CONVERT(VARCHAR,YEAR(OrderDate)) + '/' +

                  CONVERT(VARCHAR,MONTH(OrderDate)) + '/01')),

     CTE1

     AS (SELECT   B.*,

                  Moving12MonthTot = SUM(A.TotMoSales)

         FROM     CTE A

                  JOIN CTE B

                    ON A.[Month] BETWEEN DATEADD(mm,-12,b.[Month])

                    AND b.[Month]

         GROUP BY b.[Month],

                  b.TotMoSales)

SELECT   *

FROM     CTE1

ORDER BY [Month]

/*

Month       TotMoSales        Moving12MonthTot

2001-07-01  1172359.4289      1172359.4289

2001-08-01  2605514.9809      3777874.4098

2001-09-01  2073058.5385      5850932.9483

2001-10-01  1688963.2744      7539896.2227

2001-11-01  3690018.6652      11229914.8879

2001-12-01  3097637.3384      14327552.2263

2002-01-01  1605782.1915      15933334.4178

2002-02-01  3130823.0378      19064157.4556

2002-03-01  2643081.0798      21707238.5354

2002-04-01  1905833.9088      23613072.4442

2002-05-01  3758329.2949      27371401.7391

2002-06-01  2546121.9618      29917523.7009

2002-07-01  3781879.0708      33699402.7717

.......

*/

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

 

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