SQL query to compare current and previous value in a column in a history table

Question

I have a SQL Server 2008 database with a history table for recording changes in a main table. I need to report the current (latest) value of the 'rate' column, and the most recent previous value that's different from current.

So, given something like:

id   | rate   | uninteresting | updated_on   | version
-----+--------+---------------+--------------+----------
123  | 1.20   | foo           | 2010-10-18   | 1500
456  | 2.10   | bar           | 2010-10-12   | 2123
123  | 1.20   | baz           | 2010-10-10   | 1499
123  | 1.10   | baz           | 2010-10-08   | 1498
456  | 2.00   | bar           | 2010-10-11   | 2122
123  | 1.00   | baz           | 2010-08-01   | 1497
456  | 2.00   | quux          | 2010-10-05   | 2121
456  | 1.95   | quux          | 2010-09-07   | 2120

I want to produce:

id   | cur_rate | cur_ver | updated_on | prev_rate | prev_ver | prev_updated
-----+----------+---------+------------+-----------+----------+-------------
123  | 1.20     | 1500    | 2010-10-18 | 1.10      | 1498     | 2010-10-08
456  | 2.10     | 2123    | 2010-10-12 | 2.00      | 2122     | 2010-10-11

Note that I'm looking for the latest entry where the rate is different from the most recent entry.

I've tried various approaches, but either get way too many results, or none at all. Any suggestions?

Solution 1

There are a couple of ways to accomplish this. Here's one way

Declare @table  as table( 
id int, 
rate decimal(10,5) , 
uninteresting  varchar(10) ,
updated_on  date, 
version int )
INSERT INTO @table
VALUES 
(123  , 1.20   , 'foo     ' , '2010-10-18'   , 1500),
(456, 2.1, ' bar           ', ' 2010-10-12   ', 2123),
(123, 1.2, ' baz           ', ' 2010-10-10   ', 1499),
(123, 1.1, ' baz           ', ' 2010-10-08   ', 1498),
(456, 2, ' bar           ', ' 2010-10-11   ', 2122),
(123, 1, ' baz           ', ' 2010-08-01   ', 1497),
(456, 2, ' quux          ', ' 2010-10-05   ', 2121),
(456, 1.95, ' quux          ', ' 2010-09-07   ', 2120)


;WITH rates 
     AS (SELECT Row_number() OVER ( PARTITION BY curr.id, curr.rate ORDER BY curr.updated_on DESC) AS rn,
            curr.id, 
            curr.rate        cur_rate, 
            curr.version     cur_ver, 
            curr.updated_on, 
            previous.rate       prev_rate, 
            previous.version    prev_ver, 
            previous.updated_on prev_updated 
          FROM 
                @table curr
                LEFT JOIN @table previous
                ON curr.id = previous.id 
                   AND curr.rate <> previous.rate
                   AND curr.updated_on > previous.updated_on 

   ) 
   SELECT 
           id, 
           cur_rate, 
           cur_ver, 
           updated_on, 
           prev_rate, 
           prev_ver, 
           prev_updated 
    FROM 
           rates 
    WHERE 
           rn = 1

produces this result

id          cur_rate cur_ver     updated_on prev_rate prev_ver    prev_updated
----------- -------- ----------- ---------- --------- ----------- ------------
123         1.00000  1497        2010-08-01 NULL      NULL        NULL
123         1.10000  1498        2010-10-08 1.00000   1497        2010-08-01
123         1.20000  1500        2010-10-18 1.10000   1498        2010-10-08
456         1.95000  2120        2010-09-07 NULL      NULL        NULL
456         2.00000  2122        2010-10-11 1.95000   2120        2010-09-07
456         2.10000  2123        2010-10-12 2.00000   2122        2010-10-11

IF you change the rn to drop the rate in the partition by e.g. ( PARTITION BY curr.id ORDER BY curr.updated_on DESC) AS rn,

you get

id          cur_rate cur_ver     updated_on prev_rate prev_ver    prev_updated
----------- -------- ----------- ---------- --------- ----------- ------------
123         1.20000  1500        2010-10-18 1.10000   1498        2010-10-08
456         2.10000  2123        2010-10-12 2.00000   2122        2010-10-11

Solution 2

For some reason based on the way I was inserting on my test it didn't work. I had to add the previous.date in the row_number order by like this:

ROW_NUMBER() OVER (PARTITION BY curr.id, curr.status_id ORDER BY curr.row_created_date DESC, previous.row_created_date DESC) AS rn,

My scenario is slightly different and I needed to also be able to go back and forth with my 'status' as it might change. Here is the code which worked for me.

DECLARE @mytemptable TABLE
(
    tableid INT IDENTITY(1,1) PRIMARY KEY,
    id INT,
    status_id INT,
    [user_id] INT,
    row_created_date DATE
)

INSERT INTO @mytemptable VALUES (112266980, 1, 5, GETDATE()-21);
INSERT INTO @mytemptable VALUES (112266980, 2, 5, GETDATE()-14);
INSERT INTO @mytemptable VALUES (112266980, 3, 6, GETDATE()-7);
INSERT INTO @mytemptable VALUES (112266980, 4, 8, GETDATE());
INSERT INTO @mytemptable VALUES (112277777, 1, 5, GETDATE()-21);
INSERT INTO @mytemptable VALUES (112277777, 2, 5, GETDATE()-14);
INSERT INTO @mytemptable VALUES (112277777, 3, 5, GETDATE()-6);
INSERT INTO @mytemptable VALUES (112266666, 1, 5, GETDATE()-40);
INSERT INTO @mytemptable VALUES (112266666, 2, 5, GETDATE()-30);
INSERT INTO @mytemptable VALUES (112266666, 3, 5, GETDATE()-25);
INSERT INTO @mytemptable VALUES (112266666, 2, 5, GETDATE()-20);

SELECT * FROM @mytemptable ORDER BY id, row_created_date DESC

;WITH statuses
          AS (
              SELECT
                ROW_NUMBER() OVER (PARTITION BY curr.id, curr.status_id, curr.row_created_date ORDER BY curr.row_created_date DESC, previous.row_created_date DESC) AS rn,
                curr.id,
                curr.status_id curr_status_id,
                curr.user_id AS curr_user_id,
                curr.row_created_date AS curr_datetime,
                previous.status_id prev_status_id,
                previous.user_id AS prev_user_id,
                previous.row_created_date AS prev_datetime
              FROM
                @mytemptable AS curr
                LEFT JOIN @mytemptable AS previous
                    ON curr.id = previous.id
                       AND curr.status_id <> previous.status_id
                       AND curr.row_created_date > previous.row_created_date
             )
    SELECT
        id,
        curr_status_id,
        curr_user_id,
        curr_datetime,
        prev_status_id,
        prev_user_id,
        prev_datetime
    FROM
        statuses
    WHERE
        rn = 1
    ORDER BY
        id, curr_datetime DESC

Comments

[Val]

thanks for the quick response, but I'm having trouble with this. I get 'invalid column name' for all the columns specified in the (select row_number() over () as ... ) except for rn. Of course, the names I gave in the example don't exactly match my DB columns, but I've replaced the values and gone over it several times; I'm sure I have the right table and column names. I also restructured the query like with rate as ( select rn = row_number() over (partition...), id, rate, ... ), same problem.

[JNK]

This wouldn't show him the previous rate different than the current one, just the second-most-recent rate

[Conrad Frix]

@val sorry for all the edits it took awhile for me to get the sample data correct as well as the consistent outputs for full history and lastest history

[Val]

@Conrad, NP, thanks for taking the time to work on this. I'm learning a lot; I haven't used partitions much. Right now, this query is running very slowly against my DB, but the results look good. Thanks!