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