12-month moving total

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

.......

*/