# Thread: Calculating Over Top N Values Per Group

1. Registered User
Join Date
Jul 2012
Posts
1

## Unanswered: Calculating Over Top N Values Per Group

I have a access table with time series data like this:
loc | date | value
A 2/11/07 50
A 2/12/07 45
A 2/13/07 23
B 2/11/07 34
B 2/12/07 46
B 2/13/07 56
C ....... ...
...
D..........
.....
And i want to get the Z, (value - avg(values)/stDev(values), values of each group over different time periods so the 20 z values would consider values over the last 20 days, the 60 day over the last 60 days etc. And i also want to select the z values on the latest day so the result would look like this:

loc | date | value | 20Day zValue | 60Day ZValue | 120 day Zvalue
A 2/13/07 23 .04 .09 .6
B 2/13/07 56 .87 .54 .96
C .....................

it tried the following but i kept getting error messages:

SELECT
a.*,
b.20Day_zValue,
c.60Day_zValue,
d.120Day_zValue
FROM
(
SELECT aa.loc, aa.date, aa.value
FROM tbl aa
INNER JOIN
(
SELECT loc, MAX(date) AS maxdate
FROM tbl
GROUP BY loc
) bb ON aa.loc = bb.loc AND aa.date = bb.maxdate
) a
INNER JOIN
(
SELECT loc, (value AVG(value))/StDev(value) AS 20Day_zValue
FROM tbl
WHERE date >= DateAdd('d', -20, Date())
GROUP BY loc
) b ON a.loc = b.loc
INNER JOIN
(
SELECT loc, (value - AVG(value))/StDev(value) AS 60Day_zValue
FROM tbl
WHERE date >= DateAdd('d', -60, Date())
GROUP BY loc
) c ON a.loc = c.loc
INNER JOIN
(
SELECT loc, (value - AVG(value))/StDev(value) AS 120Day_zValue
FROM tbl
WHERE date >= DateAdd('d', -120, Date())
GROUP BY loc
) d ON a.loc = d.loc
Last edited by kcenac; 07-22-12 at 12:12.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•