Oracle 21c

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

CREATE TABLE CASE_DEPARTMENTS(
  STAY_ID NUMBER GENERATED by default on null as IDENTITY,
  BEGINNING TIMESTAMP,
  ENDING TIMESTAMP,
  DEPARTMENT VARCHAR2(50),
  ROOM VARCHAR2(50) NULL,
  BED VARCHAR2(50) NULL
)
INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT, ROOM, BED) 
VALUES (TO_DATE('13-08-2021 10:42:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('15-08-2021 14:34:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'HAEM', 
        '123', 
        '456');
1 rows affected

INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT, ROOM, BED) 
VALUES (TO_DATE('13-08-2021 14:34:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('15-08-2021 09:22:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'HAEM', 
        '345', 
        '789');
  
1 rows affected

INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT, ROOM, BED) 
VALUES (TO_DATE('15-08-2021 09:22:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('28-08-2021 21:09:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'ACH', 
        '111', 
        '222');
1 rows affected

INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT) 
VALUES (TO_DATE('28-08-2021 21:09:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('01-09-2021 13:34:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'HAEM');
1 rows affected

SELECT * FROM CASE_DEPARTMENTS


STAY_ID
BEGINNING
ENDING
DEPARTMENT
ROOM
BED


1
13-AUG-21 10.42.00.000000
15-AUG-21 14.34.00.000000
HAEM
123
456

2
13-AUG-21 14.34.00.000000
15-AUG-21 09.22.00.000000
HAEM
345
789

3
15-AUG-21 09.22.00.000000
28-AUG-21 21.09.00.000000
ACH
111
222

4
28-AUG-21 21.09.00.000000
01-SEP-21 13.34.00.000000
HAEM
null
null


SELECT 
  DEPARTMENT,
  LEAD(cd.DEPARTMENT) OVER (ORDER BY BEGINNING) AS "NextDep",
  BEGINNING,
  ENDING
FROM
 CASE_DEPARTMENTS cd


DEPARTMENT
NextDep
BEGINNING
ENDING


HAEM
HAEM
13-AUG-21 10.42.00.000000
15-AUG-21 14.34.00.000000

HAEM
ACH
13-AUG-21 14.34.00.000000
15-AUG-21 09.22.00.000000

ACH
HAEM
15-AUG-21 09.22.00.000000
28-AUG-21 21.09.00.000000

HAEM
null
28-AUG-21 21.09.00.000000
01-SEP-21 13.34.00.000000

STAY_ID	BEGINNING	ENDING	DEPARTMENT	ROOM	BED
1	13-AUG-21 10.42.00.000000	15-AUG-21 14.34.00.000000	HAEM	123	456
2	13-AUG-21 14.34.00.000000	15-AUG-21 09.22.00.000000	HAEM	345	789
3	15-AUG-21 09.22.00.000000	28-AUG-21 21.09.00.000000	ACH	111	222
4	28-AUG-21 21.09.00.000000	01-SEP-21 13.34.00.000000	HAEM	null	null