MySQL 8.0

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

CREATE TABLE assessments (
  `id` int,
  `device_id` int,
  `created_at` datetime
);

INSERT INTO assessments (`id`, `device_id`, `created_at`) VALUES (1, 1, '2022-07-09 22:56:00');
INSERT INTO assessments (`id`, `device_id`, `created_at`) VALUES (2, 2, '2022-07-10 22:56:06');
INSERT INTO assessments (`id`, `device_id`, `created_at`) VALUES (3, 1, '2022-07-11 22:56:11');
INSERT INTO assessments (`id`, `device_id`, `created_at`) VALUES (4, 2, '2022-07-12 22:56:17');
INSERT INTO assessments (`id`, `device_id`, `created_at`) VALUES (5, 2, '2022-07-13 22:56:24');

CREATE TABLE results (
  `assessment_id` int,
  `test` enum('A','B'),
  `result` enum('PASS','FAIL')
);

INSERT INTO results (`assessment_id`, `test`, `result`) VALUES (1, 'A', 'PASS');
INSERT INTO results (`assessment_id`, `test`, `result`) VALUES (2, 'B', 'FAIL');
INSERT INTO results (`assessment_id`, `test`, `result`) VALUES (3, 'A', 'FAIL');
INSERT INTO results (`assessment_id`, `test`, `result`) VALUES (4, 'B', 'PASS');
INSERT INTO results (`assessment_id`, `test`, `result`) VALUES (5, 'B', 'PASS');

device_id	test	prev_assessment_id	prev_result	prev_value_date	next_assessment_id	next_result	next_value_date
1	A	1	PASS	2022-07-09 22:56:00	3	FAIL	2022-07-11 22:56:11
2	B	2	FAIL	2022-07-10 22:56:06	4	PASS	2022-07-12 22:56:17