Jake wrote:
> Hi,
>
> I have a stock quote table:
>
> SQL> desc quote
> Name Null? Type
> ----------------------------------------- -------- ------------------------
> ----
> ID NOT NULL NUMBER
> SECURITYID NUMBER
> BID NUMBER
> ASK NUMBER
> LAST NUMBER
> VOLUME NUMBER
> QUOTEDATE DATE
>
> what I want to do is for each quote, select all the quotes that are between
> the quotedate of the row I'm on and quotedate plus 1 min. from that group I
> need to find the open (first quote), min(quote) low, max(quote) hi, and last
> quote in the group. I can find open, hi, low but don't know how to find the
> last quote. Here's what I've got so far
>
> select a.quotedate, min(a.last) open, min(b.last) lo, max(b.last) hi
> from quote a,
> quote b
> where a.securityid = b.securityid
> and b.quotedate >= a.quotedate
> and b.quotedate <= a.quotedate + 120/24/60/60
> group by a.quotedate
>
> Does anyone know how to find the final quote that is <= 60/24/60/60 + the
> first quote?
>
> thanks
> Jay
>
>
Take a look on SQL for analysis, analytic functions rollup and cube.
They were made for this kind of 'dataware housing/analysis'.
Set up the moving window on quotedate - and your are done.
/Svend Jensen
PS: remove the spamkiller [s.] in reply address.
Received on Sun Aug 31 2003 - 04:13:17 CDT
