CODEWITHSUNDEEP
sqlsql-servert-sql
faujong
faujong

Reputation: 1129

SQL Server query to get first and last value

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

Upvotes: 4

Views: 9808

Answers (5)

Steven Moseley
Steven Moseley

Reputation: 16325

Using my Scalar Aggregate Reduction technique (per my answer here SQL Query to get column values that correspond with MAX value of another column?) you could very simply perform this in a single grouped query without CTEs or partitions.

Here's the query:

SELECT FieldID, ChangeField,
    MIN(ChangeDate) AS FirstChangeDate,
    SUBSTRING(MIN(CONCAT(ChangeDate, OldValue)), 20) AS FirstOldValue,
    SUBSTRING(MIN(CONCAT(ChangeDate, NewValue)), 20) AS FirstNewValue,
    MAX(ChangeDate) AS LastChangeDate,
    SUBSTRING(MAX(CONCAT(ChangeDate, OldValue)), 20) AS LastOldValue,
    SUBSTRING(MAX(CONCAT(ChangeDate, NewValue)), 20) AS LastNewValue
FROM MyTable
GROUP BY FieldID, ChangeField;

There's also the added benefit of probably 3-4x the performance over the other answers provided above.

More on this technique here: https://www.stevenmoseley.com/high-performance-correlated-aggregate-sql-queries-without-ctes

Upvotes: 0

Aura
Aura

Reputation: 1307

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;

Upvotes: 6

EzLo
EzLo

Reputation: 14189

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

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can try a query like below:

see live demo

;with cte as
( 
    select
    *,
    rn1= row_number() over (partition by FieldID,ChangeField order by ChangeDate asc),
    rn2= row_number() over (partition by FieldID,ChangeField order by ChangeDate desc)
    from
    Tbl
)

select 
    FieldId,
    ChangeField,
    ChangeDate=MAX(ChangeDate),
    Oldvalue= MAX(Case when rn1=1 then Oldvalue end),
    NewValue= MAX(Case when rn2=1 then NewValue end)
from cte 
    where  rn1=1 or rn2=1
group by 
    FieldId,ChangeField

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use first_value() and last_value ()

select distinct * 
from (
  select FieldID, ChangeField  
       first_value(OldValue) over (partition by FieldID, ChangeField order by ChangeDate) OldValue,
       last_value(OldValue) over (partition by FieldID, ChangeField order by ChangeDate) NewValue,
       last_value(OldValue) over (partition by FieldID, ChangeField order by ChangeDate) dtChangeDate
  from table t
) t;

Upvotes: 0

Related Questions