Re: I need help with this sql

From: Jake <me_at_heyjay.com>
Date: Sun, 31 Aug 2003 23:34:59 -0500
Message-ID: <biuia9$6jv$1@bob.news.rcn.net>

Ok, I've got my rolling window. BUT, is there anyway to do this without specifying the window over and over for each column? That is, I want max(last), min(last), first_value(last), last_value(last) ALL for the same rolling window.

thanks
Jay

select

    quotedate,
    max(last) over

        (order by quotedate asc
         range 60/24/60/60 preceding) hi,
    min(last) over
        (order by quotedate asc
         range 60/24/60/60 preceding) low,
    first_value(last) over
        (order by quotedate asc
         range 60/24/60/60 preceding) open,
    last_value(last) over
        (order by quotedate asc
         range 60/24/60/60 preceding) close

    from quote
where quotedate > sysdate - 30
order by quotedate asc

"Svend Jensen" <svend.s.jensen_at_it.dk> wrote in message news:3f51bc17$0$32525$edfadb0f_at_dread16.news.tele.dk...
> 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 - 23:34:59 CDT