Re: finding the next date

liorlew wrote:
> Hello,
>
> I have the following problem. this is my data
>
>
> SEQ DATE
> 1 1-JAN-2008
> 2 1-JAN-2008
> 3 4-JAN-2008
> 4 4-JAN-2008
> 5 4-JAN-2008
> 6 4-JAN-2008
> 7 6-JAN-2008
>
> I need to update each row with the next date from the table:
> SEQ DATE THE_DATE_TO_PUT
> 1 1-JAN-2008 4-JAN-2008
> 2 1-JAN-2008 4-JAN-2008
> 3 4-JAN-2008 6-JAN-2008
> 4 4-JAN-2008 6-JAN-2008
> 5 4-JAN-2008 6-JAN-2008
> 6 4-JAN-2008 6-JAN-2008
> 7 6-JAN-2008
>
> The problem is that the lead function with Range doesn't work:
> lead(date) over(order by date range BETWEEN INTERVAL '1' day
> preceding
> AND INTERVAL '1' day following)
>
> I try to use max(date) with the same windowing but got:
>
> SEQ DATE THE_DATE_TO_PUT
> 1 1-JAN-2008 1-JAN-2008 <-- not good
> 2 1-JAN-2008 4-JAN-2008
> 3 4-JAN-2008 4-JAN-2008 <-- not good
> 4 4-JAN-2008 4-JAN-2008 <-- not good
> 5 4-JAN-2008 4-JAN-2008 <-- not good
> 6 4-JAN-2008 6-JAN-2008
> 7 6-JAN-2008
>
> any ideas?
>
> Thanks,
> Lior Lew

Look at LAG and LEAD in the docs at http://tahiti.oracle.com.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org