Binaryworld - Moving Average Example #1. ... [ SQL Server -> T-SQL ]

This example shows how to solve the Moving Average problem using a correlated query.

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