--1. Summarize the data
SELECT EntryDate, Sum(TrnValue) as TotalDailyValue
INTO #query1
FROM tblInvMovement
GROUP BY EntryDate;
--2. Find the dense rank of the data
SELECT A.EntryDate,A.TotalDailyValue, Count(*) as dense_rank
INTO #query2
FROM #query1 A JOIN #query1 B
ON B.EntryDate <= A.EntryDate
GROUP BY A.EntryDate,A.TotalDailyValue
ORDER BY dense_rank;
--3. Find the change based on the ranked data
-- This example is a 3day period
SELECT A.dense_rank,A.EntryDate, ISNULL(A.TotalDailyValue-B.TotalDailyValue,0) AS Change
FROM #query2 A LEFT JOIN #query2 B
ON B.dense_rank = A.dense_rank - 3
ORDER BY A.dense_rank
-------------------------------------------------------------
-- To do this in one query in MSSQL we could use:
-------------------------------------------------------------
WITH Part1
AS
(
SELECT EntryDate, SUM(TrnValue) as TotalDailyValue,
DENSE_RANK() OVER(Order By EntryDate) as RowNum
FROM tblInvMovement
GROUP BY EntryDate
)
SELECT A.EntryDate,ISNULL(A.TotalDailyValue-B.TotalDailyValue,0) AS Change
FROM Part1 A LEFT JOIN Part1 B
ON B.RowNum = A.RowNum - 3
ORDER BY A.EntryDate
-------------------------------------------------------------
-- 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
-- The method here is easily adaptable to MS Access.
-------------------------------------------------------------