TSQL Calculate difference between oldest and newest row-CodePudding

I'm trying to display how much a user's scores have changed between their oldest and most recent entries.

Given data:

Timestamp           |    Score    |  User 
2021-08-25 10:22:00 |    1300     |   A
2021-08-24 09:38:00 |    1451     |   A
2021-08-21 21:53:00 |    1381     |   B
2021-08-21 17:17:00 |    1129     |   B
2021-08-24 14:32:00 |    1278     |   A
2021-08-21 13:21:00 |    1401     |   B

I want to produce an output that looks like:

User  | CurrentScore |    Change   |   Timestamp
  A   |     1300     |     -151    |   2021-08-25 10:22:00
  B   |     1381     |     -19     |   2021-08-21 21:53:00

I can display the oldest row and the newest row, but I struggle to bring the two together in a single output row with a calculated column. Any help is apprecriated.

CodePudding user response：

Please check if this fit your needs, and please next time instead of describing the table provide DDL DML (Queries to create the table and insert some sample data)

DDL DML : should have being provided by the OP!

CREATE TABLE QQ(
    [Timestamp] DATETIME2,
    Score INT,
    [User] VARCHAR(10)
)

INSERT QQ([Timestamp],Score,[User]) VALUES
('2021-08-25 10:22:00',1300,'A'),
('2021-08-24 09:38:00',1451,'A'),
('2021-08-21 21:53:00',1381,'B'),
('2021-08-21 17:17:00',1129,'B'),
('2021-08-24 14:32:00',1278,'A'),
('2021-08-21 13:21:00',1401,'B')
GO

Solution

;With MyCTE as (
    SELECT [user], Score, 
        [Timestamp] = FIRST_VALUE ([Timestamp]) OVER (PARTITION BY [user] ORDER BY [Timestamp] DESC), 
        FirstScore = FIRST_VALUE (Score) OVER (PARTITION BY [user] ORDER BY [Timestamp]), 
        CurrentScore = FIRST_VALUE (Score) OVER (PARTITION BY [user] ORDER BY [Timestamp] DESC)
    FROM QQ
) 
SELECT DISTINCT [user], CurrentScore, Change = CurrentScore - FirstScore, [Timestamp]
FROM MyCTE
GO

DDL DML : should have being provided by the OP!

Solution

Result