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 moving averages?

Execute the following T-SQL script in Microsoft SQL Server Management Studio Query Editor to calculate the 2-month moving averages on the number of vendors supplying bicycle parts to AdventureWorks Cycles, a fictitious mountain bike manufacturer:

-- T-SQL 60-day moving average calculation
USE AdventureWorks2008;
 
DECLARE @VendorsByMonth Table
            (Sequence int , [Year] int ,[Month] int,
             UniqueVendors int)
 
INSERT @VendorsByMonth
SELECT ROW_NUMBER() OVER
                (ORDER BY year(OrderDate), month(OrderDate)),
       year(OrderDate), month(OrderDate), 0
FROM Purchasing.PurchaseOrderHeader
GROUP BY year(OrderDate), month(OrderDate)
 
 
DECLARE @i int
SET @i = 2
WHILE ( @i <= (SELECT MAX(Sequence) FROM @VendorsByMonth))
BEGIN
      UPDATE m SET m.UniqueVendors = (SElect count(distinct VendorID )
      FROM Purchasing.PurchaseOrderHeader poh
      WHERE m.[Year] = Year(poh.OrderDate)
            and m.[Month] = Month(poh.OrderDate)
            OR mp.[Year] = Year(poh.OrderDate)
            and mp.[Month] = Month(poh.OrderDate))
      FROM @VendorsByMonth m
      CROSS JOIN @VendorsByMonth mp
            WHERE m.Sequence = @i
            AND mp.Sequence = @i -1
 
      SET @i= @i + 1
END  -- WHILE
 
SELECT * FROM @VendorsByMonth
ORDER BY Sequence
GO
/*
Sequence    Year  Month UniqueVendors
1           2001  5     0
2           2002  1     28
3           2002  2     64
4           2002  3     68
5           2002  4     64
6           2002  5     56
7           2002  6     20
8           2002  7     48
9           2002  8     52
10          2002  9     36
11          2002  10    48
12          2002  11    44
13          2003  3     40
14          2003  5     56
15          2003  6     79
16          2003  7     68
17          2003  8     24
18          2003  9     79
19          2003  10    79
20          2003  11    79
21          2003  12    82
22          2004  1     82
23          2004  2     81
24          2004  3     82
25          2004  4     81
26          2004  5     81
27          2004  6     81
28          2004  7     81
29          2004  8     81
30          2004  9     79
31          2004  10    49

*/

 

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