Re: Windowing function - interval

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_at_msn.com (John Fava) wrote in message news:<9f8b5944.0308080627.226e8089_at_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
Received on Fri Aug 08 2003 - 16:17:59 CDT