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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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