Rolling Months

[buzzmcduffie]

I need to create a rolling 3 month average. I can do this using the following:

Where (((DateDiff("m",[curr_MonthYear],[LoggedMonthYear])) between -2 and 0))

However, I really need the following:
if there are no records for month -1 and/or month -2, go to the last 2
months that have a record.

Any help is appreciated!!

Thanks

 

[John W. Vinson]

I need to create a rolling 3 month average. I can do this using the following:

Where (((DateDiff("m",[curr_MonthYear],[LoggedMonthYear])) between -2 and 0))

However, I really need the following:
if there are no records for month -1 and/or month -2, go to the last 2
months that have a record.

So... if there is no data between December 1997 and today, you want to see
that month's data? What if there is no previous month WITH data? What is the
nature of the data: will there be one record per month or many?

You're going to need some sort of probably rather snarky subquery to do this!

 

[buzzmcduffie]

John - I'm stuck!

[MonthYear] = mmyyyy
[AccuracyScore] = %

I have 1 record per month. I need an average of the last three months'
accuracy score no matter when they occur. For instance:

Jun - 10%
May - 10%
Jun - null
Jul - null
Aug - 10%

need average of Jun, May, Aug (since Jun and Jul were null). If there are
no previous records then null. If there is just this month's record (August)
then = this month's accuracy %. Next month will begin to build from there
(avg of Aug and Sept)

Any help is appreciated!

I need to create a rolling 3 month average. I can do this using the following:

Where (((DateDiff("m",[curr_MonthYear],[LoggedMonthYear])) between -2 and 0))

However, I really need the following:
if there are no records for month -1 and/or month -2, go to the last 2
months that have a record.

So... if there is no data between December 1997 and today, you want to see
that month's data? What if there is no previous month WITH data? What is the
nature of the data: will there be one record per month or many?

You're going to need some sort of probably rather snarky subquery to do this!

 

[Stefan Hoffmann]

hi,

I have 1 record per month. I need an average of the last three months'
accuracy score no matter when they occur. For instance:

Basically, you need something like that:

SELECT Avg(Score)
FROM (
SELECT TOP 3 Score
FROM yourTable
WHERE NOT IsNull(Score)
ORDER BY YearPart, MonthPart
) Q


mfG
--> stefan <--

 

[buzzmcduffie]

I'm not getting it to work. I forgot to include that I have a record for each
emp so I need it to give me the top 3 for each emp. Can you be a bit more
specific.

 

[Stefan Hoffmann]

hi,

I'm not getting it to work. I forgot to include that I have a record for each
emp so I need it to give me the top 3 for each emp. Can you be a bit more
specific.

Can you? Post your actual SQL and your table layout...

mfG
--> stefan <--

 

[buzzmcduffie]

Here is my query of emps and their monthly accuracy score:

SELECT qryQAWeightedAverage.Area, qryQAWeightedAverage.Task,
qryQAWeightedAverage.EmployeeID, qryQAWeightedAverage.LoggedMonthYear,
Sum(qryQAWeightedAverage.WeightedAverage) AS AccuracyScore

FROM qryQAWeightedAverage

GROUP BY qryQAWeightedAverage.Area, qryQAWeightedAverage.Task,
qryQAWeightedAverage.EmployeeID, qryQAWeightedAverage.LoggedMonthYear

ORDER BY qryQAWeightedAverage.Area, qryQAWeightedAverage.Task,
qryQAWeightedAverage.EmployeeID, qryQAWeightedAverage.LoggedMonthYear;

I'm trying to get the employee's average of their last 3 monthly scores
regardless of when they occurred. Employees are grouped by their area and
task so if they change tasks their scores start over again and likewise if
they stay with the same task their scores stay rolling.

Thanks Stefan!

 

[Stefan Hoffmann]

hi,

Here is my query of emps and their monthly accuracy score:

SELECT qryQAWeightedAverage.Area, qryQAWeightedAverage.Task,
qryQAWeightedAverage.EmployeeID, qryQAWeightedAverage.LoggedMonthYear,
Sum(qryQAWeightedAverage.WeightedAverage) AS AccuracyScore

FROM qryQAWeightedAverage

GROUP BY qryQAWeightedAverage.Area, qryQAWeightedAverage.Task,
qryQAWeightedAverage.EmployeeID, qryQAWeightedAverage.LoggedMonthYear

ORDER BY qryQAWeightedAverage.Area, qryQAWeightedAverage.Task,
qryQAWeightedAverage.EmployeeID, qryQAWeightedAverage.LoggedMonthYear;

Save this query, I will refer to it as qryScore.

This should work:

SELECT
o.EmployeeID,
(
SELECT Avg(Q.AccuracyScore)
FROM (
SELECT TOP 3 i.AccuracyScore
FROM qryScore i
WHERE i.EmployeeID = o.EmployeeID
ORDER BY i.LoggedMonthYear DESC) Q
) AS avgScore
FROM qryScore o
GROUP BY o.EmployeeID


mfG
--> stefan <--