-------------------------------------------------------------
--How to calculate the moving average of a column of data
-------------------------------------------------------------
-- If there is no proper defined criteria on which to base the
-- moving average, eg: by date, then we must summarize the data
-- and then find the dense_rank on the grouped data. The moving
-- average
-- can then be calculated on the dense rank of the data
--1. Summarize the data
SELECT EntryDate, SUM(TrnValue) as TotalValue
INTO #query1
FROM tblInvMovement
GROUP BY EntryDate;
--2. Find the dense rank of the data
SELECT A.EntryDate,A.TotalValue, Count(*) as dense_rank
INTO #query2
FROM #query1 A JOIN #query1 B
ON B.EntryDate <= A.EntryDate
GROUP BY A.EntryDate,A.TotalValue;
--3. Find the Moving average based on the ranked data
-- This example is a 3day moving average
SELECT A.EntryDate, A.TotalValue, ISNULL(Avg(B.TotalValue),0) as MovingAverage
FROM #query2 A LEFT JOIN #query2 B
ON B.dense_rank >= A.dense_rank - 3
AND B.dense_rank < A.dense_rank
GROUP BY A.EntryDate, A.TotalValue
ORDER BY A.EntryDate
-------------------------------------------------------------
-- To do this in one query in MSSQL we could use:
-------------------------------------------------------------
WITH Part1
AS
(
SELECT EntryDate, SUM(TrnValue) as TotalValue,
DENSE_RANK() OVER(Order By EntryDate) as RowNum
FROM tblInvMovement
GROUP BY EntryDate
)
SELECT A.EntryDate, A.TotalValue, ISNULL(Avg(B.TotalValue),0) as MovingAverage
FROM Part1 A LEFT JOIN Part1 B
ON B.RowNum >= A.RowNum - 3
AND B.RowNum < A.RowNum
GROUP BY A.EntryDate, A.TotalValue
ORDER BY A.EntryDate