Get previous value of a record and display it along with the current

Question

I have a table that displays the changes in Conditions (condition_id) of Rooms (room_id) and when did it happened (date_registered). SQL Server 2008-R2.

I want to build a query that for a given date period displays the changes in room conditions of every room that changed next to its previous condition

Here is an example

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');

I would like to end up with a result like that:

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

Thank you

What does the output look like if a room have more than 2 condition rows? — Tim Biegeleisen, Jun 22 '23 at 08:32
You can use a correlated subquery to get previous conditionID. Or perhaps look into upgrading — siggemannen, Jun 22 '23 at 09:04
I strongly recommend you upgrade. SQL2008 went completely out of support 4 years ago — Charlieface, Jun 22 '23 at 09:17

score 1 · Accepted Answer · answered Jun 22 '23 at 09:10

In the absence of LAG(), we can instead try a self join with the help of ROW_NUMBER():

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;

Demo

score 1 · Answer 2 · answered Jun 22 '23 at 09:13

I was able to achieve the required result with a CTE I have tested it on 2008 compliantly level and it works.

DECLARE @StartDate datetime = '2018-12-07 00:00:00.000';
DECLARE @EndDate datetime = '2018-12-12 23:59:59.997';

WITH RoomChanges AS (
    SELECT
        room_id,
        condition_id,
        date_registered,
        ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered ASC) AS rn
    FROM @tbl
    WHERE date_registered >= @StartDate
      AND date_registered <= @EndDate
)
SELECT
    rc.room_id,
    prev.condition_id AS old_condition_id,
    rc.condition_id,
    rc.date_registered
FROM RoomChanges rc
LEFT JOIN RoomChanges prev ON rc.room_id = prev.room_id AND rc.rn = prev.rn + 1
WHERE rc.rn > 1
ORDER BY rc.date_registered;

Get previous value of a record and display it along with the current

2 Answers2

Demo