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

Sometimes the solution can be more simple than expected:

update my_table t1 set date_to_put=
(select min(date) from my_table t2
where t2.date > t1.date)

I don't know how this will perform, but it works.

I advise to rename the column "DATE" to a non reserved word by the way.

Shakespeare Received on Fri Apr 04 2008 - 03:27:02 CDT