Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
0


create table tb1 
(
  log_id int,
  dt date,
  room int,
  usr varchar2(100),
  minutes int,
  room_in varchar(5 char),
  room_out varchar(5 char),
  turnaround int
);
begin
  insert into tb1 values ('12345', date '2023-08-01', 1, 'DOE, JANE', 50, '7:35', '8:30', 0);
  insert into tb1 values ('12345', date '2023-08-01', 1, 'DOE, JANE', 5, '7:35', '8:30', 0);
  insert into tb1 values ('67890', date '2023-08-01', 1, 'DOE, JOHN', 20, '9:04', '9:24', 34);
  insert into tb1 values ('12111', date '2023-08-01', 1, 'PATEL, NANCY', 28, '10:52', '11:20', 88);
  insert into tb1 values ('12222', date '2023-08-01', 1, 'DREW, AMBER', 100, '11:41', '15:21', 21);
end;
/
1 rows affected

select
  log_id, dt, room, usr, minutes, room_in, room_out,
  nvl(lead(turnaround) over (order by room, dt, lpad(room_in, 5, '0')), 0) as turnaround
from tb1
order by room, dt, lpad(room_in, 5, '0');


LOG_ID
DT
ROOM
USR
MINUTES
ROOM_IN
ROOM_OUT
TURNAROUND


12345
01-AUG-23
1
DOE, JANE
50
7:35
8:30
0

12345
01-AUG-23
1
DOE, JANE
5
7:35
8:30
34

67890
01-AUG-23
1
DOE, JOHN
20
9:04
9:24
88

12111
01-AUG-23
1
PATEL, NANCY
28
10:52
11:20
21

12222
01-AUG-23
1
DREW, AMBER
100
11:41
15:21
0


merge into tb1 using
(
  select
    log_id, dt, room, usr, minutes, room_in, room_out,
    nvl(lead(turnaround) over (order by room, dt, lpad(room_in, 5, '0')), 0) as turnaround
  from tb1
) correction on (correction.rowid = tb1.rowid)
when matched then update set tb1.turnaround = correction.turnaround;
5 rows affected

select *
from tb1
order by room, dt, lpad(room_in, 5, '0');


LOG_ID
DT
ROOM
USR
MINUTES
ROOM_IN
ROOM_OUT
TURNAROUND


12345
01-AUG-23
1
DOE, JANE
50
7:35
8:30
0

12345
01-AUG-23
1
DOE, JANE
5
7:35
8:30
34

67890
01-AUG-23
1
DOE, JOHN
20
9:04
9:24
88

12111
01-AUG-23
1
PATEL, NANCY
28
10:52
11:20
21

12222
01-AUG-23
1
DREW, AMBER
100
11:41
15:21
0

LOG_ID	DT	ROOM	USR	MINUTES	ROOM_IN	ROOM_OUT	TURNAROUND
12345	01-AUG-23	1	DOE, JANE	50	7:35	8:30	0
12345	01-AUG-23	1	DOE, JANE	5	7:35	8:30	34
67890	01-AUG-23	1	DOE, JOHN	20	9:04	9:24	88
12111	01-AUG-23	1	PATEL, NANCY	28	10:52	11:20	21
12222	01-AUG-23	1	DREW, AMBER	100	11:41	15:21	0