Results 1 to 1 of 1
  1. #1
    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 13: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
  •