Oh knowledgable SQL stars, I have a question on a query.
Given: A table with daily observations such as:
create table DailyObs ( +
LocID integer, +
ObsDate date, +
ObsValue real )
Wanted: Running averages of the observed value (obsvalue) for various
periods (3 day, 7 day, 15 day).
Is this possible with a select statement, or will I need to use a cursor?
Example:
------- Table DailyObs ----- --- Results wanted --
LocID ObsDate ObsValue 3-day 7-day 15-day
100010 12/1/2000 5.6
100010 12/2/2000 2.8
100010 12/3/2000 2.8 3.7
100010 12/4/2000 2.2 2.6
100010 12/5/2000 3.3 2.8
100010 12/6/2000 3.3 2.9
100010 12/7/2000 -2.8 1.3 2.5
100010 12/8/2000 1.1 0.5 1.8
100010 12/9/2000 -0.6 -0.8 1.3
100010 12/10/2000 4.4 1.6 1.6
100010 12/11/2000 -1.7 0.7 1.0
100010 12/12/2000 -2.2 0.2 0.2
100010 12/13/2000 -2.2 -2.0 -0.6
100010 12/14/2000 1.1 -1.1 0.0
100010 12/15/2000 2.8 0.6 0.2 1.3
100010 12/16/2000 1.1 1.7 0.5 1.0
100010 12/17/2000 2.2 2.0 0.2 1.0
100010 12/18/2000 0.0 1.1 0.4 0.8
100010 12/19/2000 -1.1 0.4 0.6 0.6
100010 12/20/2000 0.0 -0.4 0.9 0.4
100010 12/21/2000 -2.2 -1.1 0.4 0.0
100010 12/22/2000 -2.8 -1.7 -0.4 0.0
100010 12/23/2000 2.8 -0.7 -0.2 0.1
100010 12/24/2000 3.3 1.1 0.0 0.4
100010 12/25/2000 1.1 2.4 0.2 0.1
100010 12/26/2000 -1.7 0.9 0.1 0.1
100010 12/27/2000 -5.0 -1.9 -0.6 0.0
100010 12/28/2000 -1.7 -2.8 -0.6 0.0
100010 12/29/2000 -5.6 -4.1 -1.0 -0.5
100010 12/30/2000 -2.2 -3.2 -1.7 -0.8
100010 12/31/2000 -1.1 -3.0 -2.3 -0.9
I have tried the following select without success:
select locid,obsdate,count(*),avg(obsvalue) from dailyobs +
where obsdate between (obsdate-2) and obsdate group by +
locid,obsdate
TIA
Clarence
--
Clarence W. Robison, P.E.
[EMAIL PROTECTED]
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
