By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @tbl TABLE (
room_id numeric(10,0) null,
condition_id numeric(10,0) null,
date_registered datetime null
);
INSERT @tbl (room_id, condition_id, date_registered)
VALUES
(1,2,'2018-12-07 08:37:19.300'),
(2,1,'2018-12-08 08:37:19.300'),
(1,3,'2018-12-09 08:37:19.300'),
(2,2,'2018-12-10 08:37:19.300'),
(1,1,'2018-12-11 08:37:19.300');
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered) rn
FROM @tbl
)
SELECT
t1.room_id,
t1.condition_id AS old_condition_id,
t2.condition_id,
t2.date_registered
FROM cte t1
INNER JOIN cte t2
ON t2.room_id = t1.room_id AND
t2.rn = t1.rn + 1 AND
t2.condition_id <> t1.condition_id
ORDER BY
t1.date_registered;
| room_id | old_condition_id | condition_id | date_registered |
|---|---|---|---|
| 1 | 2 | 3 | 2018-12-09 08:37:19.300 |
| 2 | 1 | 2 | 2018-12-10 08:37:19.300 |
| 1 | 3 | 1 | 2018-12-11 08:37:19.300 |