Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    52

    Unhappy Unanswered: help me in the average query plzzzzzzz urgent

    Date--------|--------upstream--------|--------downstream
    ----------------------------------------------------------------
    22-May-99---------5700.0-------------------16600.0
    23-May-99---------11000.0------------------18200.0
    24-May-99---------14700.0------------------21400.0
    25-May-99---------15500.0------------------25900.0
    26-May-99---------15900.0------------------32300.0
    27-May-99---------16300.0------------------34600.0
    28-May-99---------14300.0------------------34600.0
    29-May-99---------13900.0------------------35000.0
    30-May-99---------12900.0------------------36100.0
    31-May-99---------11100.0------------------34500.0
    01-Jun-99----------11000.0------------------35000.0

    i need to build a query which take average of five dates. its like user enters any date e.g 25 may 99 and 30-May-99. so the user gets the average for 23 may , 24 may 25 may 26 may and 27 may and average for 28, 29,30 and 31. so its like averages between two specifed dates on 5 days basis.

    and one ting more lets say there is no date like 24 may

    Date--------|--------upstream--------|--------downstream
    ----------------------------------------------------------------
    22-May-99---------5700.0-------------------16600.0
    23-May-99---------11000.0------------------18200.0
    25-May-99---------15500.0------------------25900.0
    26-May-99---------15900.0------------------32300.0
    27-May-99---------16300.0------------------34600.0
    28-May-99---------14300.0------------------34600.0
    29-May-99---------13900.0------------------35000.0
    30-May-99---------12900.0------------------36100.0
    31-May-99---------11100.0------------------34500.0
    01-Jun-99----------11000.0------------------35000.0

    so wat i need is that it still calculates average for 23,24,25,26,27 but upstream and downstream values will be considered as 0 for 24 May 1999 .
    Last edited by waqas; 01-23-03 at 08:40.

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    Finding averages

    You might be better off trying to get your info using code rather than a query. Your wanting to include skipped days in the average makes things much more complex. Sorry I couldn't be of more help.
    SteveH

  3. #3
    Join Date
    Jan 2003
    Posts
    52

    Unhappy Re: Finding averages

    wat if when there is no skipping of days ?????????????!!!!!!!!!!!!! and even leave this thing for a moment that we need to move back two days, but how are we going to calculate average on 5 day basis.

    if anyone else can help me plz do so, i need this thing urgently. sp this thing, moving back and skipping thing. but for a time period do this thing.
    Last edited by waqas; 01-23-03 at 12:02.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select Form.UserDate
    , sum(upstream)/5 as avgup
    , sum(downstream)/5 as avgdown
    from YourTable
    where [Date] between
    datediff("d",-2,Form.UserDate)
    and
    datediff("d",+2,Form.UserDate)

    note no GROUP BY

    nor is it necessary for all days to be present

    the 5-day average is sum()/5, not avg()/count()

    rudy

  5. #5
    Join Date
    Jan 2003
    Posts
    52

    Thumbs up

    This query really works??????, actually i m without access at the moment. so plz don't mind the fmy question . plus i m very much excited by uur reply.
    thanx, keep up the good work, great

    but one thing more wat shuld i write inplace of form.userdate ???????
    Last edited by waqas; 01-23-03 at 14:23.

Posting Permissions

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