Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: JusungYang@yahoo.com (Jusung Yang)
Newsgroups: comp.databases.oracle.server
Subject: Re: Windowing function - interval
Date: 8 Aug 2003 14:17:59 -0700
Organization: http://groups.google.com/
Lines: 43
Message-ID: <130ba93a.0308081317.15025eae@posting.google.com>
References: <9f8b5944.0308080627.226e8089@posting.google.com>
NNTP-Posting-Host: 64.168.23.201
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1060377480 20464 127.0.0.1 (8 Aug 2003 21:18:00 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2003 21:18:00 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:240132

ORACLE is comparing the length between 2 dates and the interval you
supplied. Pretty much like how it works when you use 'between' in a
simple SQL statement. It is measuring the lenght of 'time', not just
the 'calendar date'. So, yes, ORACLE would indeed look back 90 'whole'
days when you say

range INTERVAL '90' DAY preceding

or you can even include hours in your interval specification like

range INTERVAL '90 5' DAY to HOUR preceding

This would get you 90 days and 5 hours.


- Jusung Yang


jmfava@msn.com (John Fava) wrote in message news:<9f8b5944.0308080627.226e8089@posting.google.com>...
> I'm not sure if the below syntax does what I think it is doing.  I
> want to look back 90 days from the "date_key" field and determine
> which is the minimum date for a give order_nbr and task.  Does the
> "Interval '90' DAY" command actually look back 90 days?  does it
> include the current date - if so, isn't that really 91 days? 
> Appreciate your help!  Thanks.
> 
> John Fava
> 
> 
> select
> order_nbr,
> task,
> date_key,
> min(date_key) over
> (PARTITION BY order_nbr, task
>                     ORDER BY date_key asc range INTERVAL '90' DAY
> preceding) as repeat
> from
> orders
> group by
> order_nbr,
> task,
> date_key