/*
This table tracks the quantity bought for a particular item.
The problem is to calculate the moving average of sales over the last 3 months
including the current month. The logic can be extended to any moving average problem.
*/
CREATE TABLE #ItemSales (
ItemID int ,
DateBought datetime ,
Quantity int ,
CONSTRAINT PK_ItemSales_ID_Bought PRIMARY KEY CLUSTERED( ItemID , DateBought )
);
INSERT INTO #ItemSales
SELECT 1 , '2000-10-20' , 62
UNION ALL
SELECT 1 , '2000-09-01' , 13
UNION ALL
SELECT 1 , '2000-11-01' , 45
UNION ALL
SELECT 1 , '2000-06-01' , 89
UNION ALL
SELECT 2 , '2000-06-01' , 37
UNION ALL
SELECT 2 , '2000-10-24' , 81
UNION ALL
SELECT 2 , '2000-10-12' , 56;
-- How the data looks, from the most recent sale for eacn item.
SELECT * FROM #ItemSales ORDER BY ItemID , DateBought DESC;
/*
ItemID DateBought Quantity
----------- ------------------------------------------------------ -----------
1 2000-11-01 00:00:00.000 45
1 2000-10-20 00:00:00.000 62
1 2000-09-01 00:00:00.000 13
1 2000-06-01 00:00:00.000 89
2 2000-10-24 00:00:00.000 81
2 2000-10-12 00:00:00.000 56
2 2000-06-01 00:00:00.000 37
*/
GO
/*
Corelated query for calculating the moving average of each item's sale over the
last 3 months including the current date.
*/
SELECT i1.ItemID , i1.DateBought ,
( SELECT AVG( i2.Quantity )
FROM #ItemSales AS i2
WHERE i2.ItemID = i1.ItemID And
DATEDIFF( mm , i2.DateBought , i1.DateBought ) Between 0 And 3
) AS MovingAverageOver3Months
FROM #ItemSales AS i1
ORDER BY i1.ItemID , i1.DateBought DESC;
-- Expected Output:
/*
ItemID DateBought MovingAverageOver3Months
----------- ------------------------------------------------------ ------------------------
1 2000-11-01 00:00:00.000 40
1 2000-10-20 00:00:00.000 37
1 2000-09-01 00:00:00.000 51
1 2000-06-01 00:00:00.000 89
2 2000-10-24 00:00:00.000 68
2 2000-10-12 00:00:00.000 68
2 2000-06-01 00:00:00.000 37
*/
DROP TABLE #ItemSales;
GO
This page was last updated on May 01, 2006 04:28 PM.