CODEWITHSUNDEEP
sqlsql-serverdifference
sharkyenergy
sharkyenergy

Reputation: 4173

SQL Server : subtract last row from first row

I have a query that returns a lot of rows. I would need the difference between the values in the last row and the first row. TimeCol is a DateTime column, all other columns are of type float.

My current (simplified) query is this:

select 
    timeCol, TotSoap_W1, TotSoap_W2, TotEnergy
from
    TotEnergy
where 
    TimeCol >= '2019-03-01 00:00:00' and timeCol < '2019-03-02 00:00:00'
order by 
    TimeCol asc

Upvotes: 0

Views: 2042

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131374

You can use the FIRST_VALUE and LAST_VALUE analytic functions :

select top 1 LAST_VALUE(TotSoap_W1) OVER (ORDER BY TimeCol ROWS
                                          BETWEEN UNBOUNDEDPRECEDING AND UNBOUNDED FOLLOWING)
             - FIRST_VALUE(TotSoap_W1)
from TotEnergy
where TimeCol >= '2019-03-01 00:00:00' and timeCol < '2019-03-02 00:00:00'
order by TimeCol asc

LAST_VALUE returnes the last value in each group/result set according to the sorting order. In this query, it would be the same as returning each row's TotSoap_W1 value. To make it return the last value in all the results, we need to add the ROWS BETWEEN UNBOUNDEDPRECEDING AND UNBOUNDED FOLLOWING clause

Upvotes: 1

Neo
Neo

Reputation: 809

This might not be the best way of doing but should do the job:

SELECT B.TotSoap_W1-A.TotSoap_W1 as TotSoap_W1, B.TotSoap_W2-A.TotSoap_W2 as TotSoap_W2, B.TotEnergy-A.TotEnergy as TotEnergy
FROM
(select TOP(1) timeCol, TotSoap_W1, TotSoap_W2, TotEnergy
from TotEnergy
where TimeCol >= '2019-03-01 00:00:00' and timeCol < '2019-03-02 00:00:00'
order by TimeCol ASC) A,
(select TOP(1) timeCol, TotSoap_W1, TotSoap_W2, TotEnergy
from TotEnergy
where TimeCol >= '2019-03-01 00:00:00' and timeCol < '2019-03-02 00:00:00'
order by TimeCol DESC
) B

Upvotes: 1

Related Questions