Trailing Twelve Month Dynamic Query

[mschorr75]

I need to develop a TTM (trailing twelve month) report that is generated
every day and includes the previous day's data. This TTM report should show
the value of all orders logged each day - averaged over a 12 month period
leading up to each day. So...

Raw Data:
Date Value
1/1/08 $5
1/2/08 $10
1/3/08 $12

 

[John Spencer]

Perhaps something like this will work for you.


SELECT A.DateField, Avg(B.ValueField) as TTM
FROM YourTable as A INNER JOIN YourTable As B
ON A.DateField > B.DateField
AND A.DateField < DateAdd("m",12,B.DateField)
GROUP BY A.DateField
ORDER BY A.DateField


If you are doing this for one specific date then you could just use

SELECT Avg(ValueField) as TTM
FROM YourTable
WHERE DateField between DateAdd("M",-12,Date()) and Date()


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[mschorr75]

Brilliant! Thanks John! Worked like a charm!

Perhaps something like this will work for you.

SELECT A.DateField, Avg(B.ValueField) as TTM
FROM YourTable as A INNER JOIN YourTable As B
ON A.DateField > B.DateField
AND A.DateField < DateAdd("m",12,B.DateField)
GROUP BY A.DateField
ORDER BY A.DateField

If you are doing this for one specific date then you could just use

SELECT Avg(ValueField) as TTM
FROM YourTable
WHERE DateField between DateAdd("M",-12,Date()) and Date()

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I need to develop a TTM (trailing twelve month) report that is generated
every day and includes the previous day's data. This TTM report should show

[quoted text clipped - 6 lines]

1/2/08 $10
1/3/08 $12