Rolling Average Calculation Problem

[david.monaghan]

I have a table with the following fields - Location, Manager,
CostCentre, Month and Headcount. What I am trying to do is a monthly
rolling average headcount by Location, Manager, CostCentre. To verify
the calculations I am using sum rather then average and the query below
is showing the sum of all records in the month rather then breaking it
down by the Location, Manager, CostCentre combinations.

SELECT test.Loc, test.[Line Mgr], test.CC, test.Month,
test.CountOfName, (SELECT round(sum(Dupe.CountOfName),0) AS
AvgCountOfName FROM [Test] AS Dupe WHERE Loc=Dupe.Loc and [Line
Mgr]=dupe.[line mgr] and cc=dupe.cc and Dupe.Month Between DateAdd("m",
-3, [test].[Month]) And [test].[Month]) AS RollingSum
FROM test
ORDER BY test.Loc, test.[Line Mgr], test.CC, test.Month;

Any help will be greatly appreciated

[David Schofield]

On 31 Jul 2005 17:28:43 -0700, da************@bigpond.com wrote:

I have a table with the following fields - Location, Manager,
CostCentre, Month and Headcount. What I am trying to do is a monthly
rolling average headcount by Location, Manager, CostCentre. To verify
the calculations I am using sum rather then average and the query below
is showing the sum of all records in the month rather then breaking it
down by the Location, Manager, CostCentre combinations.

SELECT test.Loc, test.[Line Mgr], test.CC, test.Month,
test.CountOfName, (SELECT round(sum(Dupe.CountOfName),0) AS
AvgCountOfName FROM [Test] AS Dupe WHERE Loc=Dupe.Loc and [Line
Mgr]=dupe.[line mgr] and cc=dupe.cc and Dupe.Month Between DateAdd("m",
-3, [test].[Month]) And [test].[Month]) AS RollingSum
FROM test
ORDER BY test.Loc, test.[Line Mgr], test.CC, test.Month;

Any help will be greatly appreciated

Hi
Moving averages and sums, if required in SQL, are usually done using proprietary
features, eg Oracle has a number of special features for this sort of thing.

Here is a way you can use in SQL which uses a supplementary table of integers,
which is my pet way of doing things. Create the table by pasting this code
in a module, clicking inside it and pressing F5:

Public Sub VSetup()
Dim rs As Recordset
Dim j As Long
Dim db As Database
On Error Resume Next ' (!)
Set db = DBEngine(0)(0)
db.Execute "CREATE TABLE integers (i INTEGER );"
Set rs = db.OpenRecordset("integers", dbOpenTable)
DBEngine(0).BeginTrans
For j = 0 To 9999
rs.AddNew
rs!i = j
rs.Update
Next j
DBEngine(0).CommitTrans
rs.Close
db.Execute "CREATE INDEX PrimaryKey ON integers (i) WITH PRIMARY;"
End Sub

10,000 is usually enough! (These are SQL integers, ie vba long)

Now, assume that all your months are represented by a single number - eg
you could use the mapping generated by a stored query "Vmonths"

SELECT DISTINCTROW DateAdd("m",[i],CDate("1 January 1990")) AS DateValue, Integers.i
FROM Integers
ORDER BY Integers.i;

which generates months from January 1990 to April 2823.

Then the query

SELECT test.Loc, test.[Line mgr], test.CC, test.Month AS MonthFrom, [Month]+2 AS MonthTo, Count(test.Name) AS CountOfName
FROM test INNER JOIN integers ON test.Month = integers.i
WHERE (((test.Month) Between [i] And [i]+3))
GROUP BY test.Loc, test.[Line mgr], test.CC, test.Month, [Month]+2;

should give you the name count for every three month period.
but be warned I haven't tested it properly as I haven't much data!

HTH
David

[David Schofield]

On 31 Jul 2005 17:28:43 -0700, da************@bigpond.com wrote:

I have a table with the following fields - Location, Manager,
CostCentre, Month and Headcount. What I am trying to do is a monthly
rolling average headcount by Location, Manager, CostCentre. To verify
the calculations I am using sum rather then average and the query below
is showing the sum of all records in the month rather then breaking it
down by the Location, Manager, CostCentre combinations.

SELECT test.Loc, test.[Line Mgr], test.CC, test.Month,
test.CountOfName, (SELECT round(sum(Dupe.CountOfName),0) AS
AvgCountOfName FROM [Test] AS Dupe WHERE Loc=Dupe.Loc and [Line
Mgr]=dupe.[line mgr] and cc=dupe.cc and Dupe.Month Between DateAdd("m",
-3, [test].[Month]) And [test].[Month]) AS RollingSum
FROM test
ORDER BY test.Loc, test.[Line Mgr], test.CC, test.Month;

Any help will be greatly appreciated

Hi
Moving averages and sums, if required in SQL, are usually done using proprietary
features, eg Oracle has a number of special features for this sort of thing.

Here is a way you can use in SQL which uses a supplementary table of integers,
which is my pet way of doing things. Create the table by pasting this code
in a module, clicking inside it and pressing F5:

Public Sub VSetup()
Dim rs As Recordset
Dim j As Long
Dim db As Database
On Error Resume Next ' (!)
Set db = DBEngine(0)(0)
db.Execute "CREATE TABLE integers (i INTEGER );"
Set rs = db.OpenRecordset("integers", dbOpenTable)
DBEngine(0).BeginTrans
For j = 0 To 9999
rs.AddNew
rs!i = j
rs.Update
Next j
DBEngine(0).CommitTrans
rs.Close
db.Execute "CREATE INDEX PrimaryKey ON integers (i) WITH PRIMARY;"
End Sub

10,000 is usually enough! (These are SQL integers, ie vba long)

Now, assume that all your months are represented by a single number - eg
you could use the mapping generated by a stored query "Vmonths"

SELECT DISTINCTROW DateAdd("m",[i],CDate("1 January 1990")) AS DateValue, Integers.i
FROM Integers
ORDER BY Integers.i;

which generates months from January 1990 to April 2823.

Then the query

SELECT test.Loc, test.[Line mgr], test.CC, test.Month AS MonthFrom, [Month]+2 AS MonthTo, Count(test.Name) AS CountOfName
FROM test INNER JOIN integers ON test.Month = integers.i
WHERE (((test.Month) Between [i] And [i]+3))
GROUP BY test.Loc, test.[Line mgr], test.CC, test.Month, [Month]+2;

should give you the name count for every three month period.
but be warned I haven't tested it properly as I haven't much data!

HTH
David

[David Schofield]

P.S.
That assumes you want a value once each month for the three month rolling sum, if you want a value each day
(for example) that can be done in a similar way.

[David Schofield]

P.S.
That assumes you want a value once each month for the three month rolling sum, if you want a value each day
(for example) that can be done in a similar way.