Difference between the values of multiple rows in SQL

Question

My table in SQL is like:-

RN   Name   value1  value2  Timestamp
1    Mark   110     210     20160119
1    Mark   106     205     20160115
1    Mark   103     201     20160112
2    Steve  120     220     20151218
2    Steve  111     210     20151210
2    Steve  104     206     20151203

Desired Output:-

RN  Name    value1Lag1 value1lag2   value2lag1  value2lag2
1   Mark       4             3            5        4
2   Steve      9             7            10       4

The difference is calculated from the most recent to the second recent and then from second recent to the third recent for RN 1

value1lag1 = 110-106 =4

value1lag2 = 106-103 = 3

value2lag1 = 210-205 = 5

value2lag2 = 205-201 = 4

similarly for other RN's also.

Note: For each RN there are 3 and only 3 rows.

I have tried in several ways by taking help from similar posts but no luck.

Answer 1

I've assumed that RN and Name are linked here. It's a bit messy, but if each RN always has 3 values and you always want to check them in this order, then something like this should work.

SELECT
    t1.Name
    , AVG(CASE WHEN table_ranked.Rank = 1 THEN table_ranked.value1 ELSE NULL END) - AVG(CASE WHEN table_ranked.Rank = 2 THEN table_ranked.value1 ELSE NULL END)   value1Lag1
    , AVG(CASE WHEN table_ranked.Rank = 2 THEN table_ranked.value1 ELSE NULL END) - AVG(CASE WHEN table_ranked.Rank = 3 THEN table_ranked.value1 ELSE NULL END) value1Lag2
    , AVG(CASE WHEN table_ranked.Rank = 1 THEN table_ranked.value2 ELSE NULL END) - AVG(CASE WHEN table_ranked.Rank = 2 THEN table_ranked.value2 ELSE NULL END) value2Lag1
    , AVG(CASE WHEN table_ranked.Rank = 2 THEN table_ranked.value2 ELSE NULL END) - AVG(CASE WHEN table_ranked.Rank = 3 THEN table_ranked.value2 ELSE NULL END) value2Lag2
FROM table t1
INNER JOIN
(
    SELECT
        t1.Name
        , t1.value1
        , t1.value2
        , COUNT(t2.TimeStamp) Rank
    FROM table t1
    INNER JOIN table t2
        ON t2.name = t1.name
        AND t1.TimeStamp <= t2.TimeStamp
    GROUP BY t1.Name, t1.value1, t1.value2
) table_ranked
    ON table_ranked.Name = t1.Name
GROUP BY t1.Name