Re: finding the next date

"liorlew" <liorlew_at_gmail.com> schreef in bericht news:1eb083f6-5f09-47e9-af94-027a0953664b_at_u69g2000hse.googlegroups.com...
> 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

You're welcome!

Shakespeare Received on Mon Apr 07 2008 - 13:29:16 CDT