Execute the following script in Query Editor to calculate the 2-month moving averages on the number of vendors supplying bicycle parts to AdventureWorks mountain bike manufacturer:

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

SELECT * FROM  @VendorsByMonth

WHERE Sequence > 1

ORDER BY Sequence

/* Verify one detail line

Sequence    Year  Month UniqueVendors

16    2003  7     68

select count(distinct VendorID)

from Purchasing.PurchaseOrderHeader

where Year(OrderDate)=2003

and Month(OrderDate) in (6,7)

-- 68

*/