Re: dilemma

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 13 Mar 2006 18:24:33 +0100
Message-ID: <4415aad2$0$19090$626a54ce@news.free.fr>

"Reiro" <ReiroGP_at_gmail.com> a écrit dans le message de news: 1142245606.502944.30450_at_u72g2000cwu.googlegroups.com...
| We have a test database with 2 fields STart_date_time and
| end_date_time.
| format of the fields are timestamp example: 27/OCT/05 05:50:00
|
|
| Start_date_time end date time
|
| 27/OCT/05 05:50:00 27/OCT/05 06:44:00
| 27/OCT/05 06:44:00 27/OCT/05 07:30:00
| 27/OCT/05 07:30:00
| 27/OCT/05 08:00:00
| 27/OCT/05 09:50:00
|
|
| we want the start time of row 1 to have the end time of row 2.
| Start_date_times are already
| populated in the database and End_date_time columns are empty.
|
| plz help...
|
| P.S thanx in advance.
|

SQL> select * from t order by start_time; START_TIME END_TIME

------------------ ------------------
27/OCT/05 05:50:00
27/OCT/05 06:44:00
27/OCT/05 07:30:00

27/OCT/05 08:00:00
27/OCT/05 09:50:00 5 rows selected.

SQL> update t a
  2 set end_time =

  3      ( select end_time
  4        from ( select rowid, lead(start_time) over (order by start_time) end_time
  5               from t ) b
  6        where b.rowid = a.rowid )

  7 /

5 rows updated.

SQL> select * from t order by start_time; START_TIME END_TIME

------------------ ------------------
27/OCT/05 05:50:00 27/OCT/05 06:44:00
27/OCT/05 06:44:00 27/OCT/05 07:30:00
27/OCT/05 07:30:00 27/OCT/05 08:00:00

27/OCT/05 08:00:00 27/OCT/05 09:50:00
27/OCT/05 09:50:00 5 rows selected.

Regards
Michel Cadot Received on Mon Mar 13 2006 - 11:24:33 CST