# Thread: help me in the aveage query plzzzzzzz urgent

1. Registered User
Join Date
Jan 2003
Posts
52

## 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. Registered User
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.

3. Registered User
Join Date
Jan 2003
Posts
52

## 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. SQL Consultant
Join Date
Apr 2002
Location
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. Registered User
Join Date
Jan 2003
Posts
52
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
•