SQL Server query to get first and last value

Question

I have a table with FieldID, ChangeField, OldValue, NewValue and ChangeDate like shown below:

FieldID   ChangeField  OldValue   NewValue   ChangeDate
1         interest     1.5        1.2        2018-05-01 13:00:00
1         interest     1.2        1.3        2018-05-01 14:00:00
1         quantity     2          1          2018-05-01 15:00:00
1         quantity     1          2          2018-05-01 16:00:00
1         quantity     2          3          2018-05-01 17:00:00
2         quantity     10         20         2018-05-01 18:00:00
2         quantity     20         30         2018-05-01 19:00:00

Is it possible to get the first and last changes for every ChangeField per FieldID, for example the below result ?

FieldID ChangeField     OldValue    NewValue    dtChangeDate
1       interest        1.5         1.3     2018-05-01 14:00:00  
--> The original value for interest was 1.5, the last value for interest is 1.3
1       quantity        2           3       2018-05-01 17:00:00  
--> The original value for quantity was 2, the last value for interest is 3
2       quantity        10          30      2018-05-01 19:00:00  
--> The original value for quantity was 10, the last value for interest is 30

Note that ChangeDate is always the latest ChangeDate

Answer 1

You can try the following code, I have used FIRST_VALUE() for getting first value in OldValue column and LAST_VALUE() for getting last values of the group for NewValue and dtChangeDate.

    SELECT
    FieldID,
    ChangeField,
    FIRST_VALUE(OldValue) OVER (PARTITION BY
                                    fieldID, ChangeField
                                ORDER BY FieldID
                               )  AS OldValue,
    LAST_VALUE(NewValue) OVER (PARTITION BY
                                   fieldID, ChangeField
                               ORDER BY FieldID
                              )   AS NewValue,
    LAST_VALUE(ChangeDate) OVER (PARTITION BY
                                     fieldID, ChangeField
                                 ORDER BY FieldID
                                ) AS dtChangeDate
    FROM YourTable;

Answer 2

You can use ROW_NUMBER() to get oldest/newest.

;WITH Boundaries AS
(
    SELECT
        T.FieldID,
        T.ChangeField,
        T.OldValue,
        T.NewValue,
        OldestRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.FieldID,
                T.ChangeField
            ORDER BY
                T.ChangeDate ASC),
        NewestRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.FieldID,
                T.ChangeField
            ORDER BY
                T.ChangeDate DESC)
    FROM
        YourTable AS T
)
SELECT
    T.FieldID,
    T.ChangeField,
    T.OldValue,
    OldValueDate = T.ChangeDate,
    N.NewValue,
    NewValueDate = N.ChangeDate,
FROM
    Boundaries AS T
    INNER JOIN Boundaries AS N ON 
        T.FieldID = N.FieldID AND
        T.ChangeField  = N.ChangeField
WHERE
    T.OldestRanking = 1 AND
    N.NewestRanking = 1