finding the next date
From: liorlew <liorlew_at_gmail.com>
Date: Thu, 3 Apr 2008 15:07:54 -0700 (PDT)
Message-ID: <1eb083f6-5f09-47e9-af94-027a0953664b@u69g2000hse.googlegroups.com>
Date: Thu, 3 Apr 2008 15:07:54 -0700 (PDT)
Message-ID: <1eb083f6-5f09-47e9-af94-027a0953664b@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
Received on Thu Apr 03 2008 - 17:07:54 CDT