If you're using SQL Server 2016 or later, you can use the
FIRST_VALUE[
^] and
LAST_VALUE[
^] functions. Depending on your indexes, this may be slightly more efficient.
SELECT TOP 1
FIRST_VALUE([CM1-Ea]) OVER (ORDER BY [DateTime])
- LAST_VALUE([CM1-Ea]) OVER (ORDER BY [DateTime] RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
As Diff
FROM
[DBScadaCloudPrutulLogger].[dbo].[TBL_ENERGII]
WHERE
[DateTime] Between @DataStart And @DataStop
;
NB: The
RANGE BETWEEN
clause is required on the
LAST_VALUE
call, otherwise the function will not take any rows after the first row into account.