how to select first and last row in 1 query after Filtering and then carry out calculation between the values of two values in one query

I’m using T-SQL 2014

Suppose I have a stock price chart as follow

I want to write efficient code for a stored function to display the Open price at the start, Close price at the end, and the difference between Close and Open. Is it possible to do that in one query? The query seems easy but it turned out extremely difficult. My first problem is to display the first row and last row in one query.

My attempt is like this

create function GetVolatilityRank(@from date, @to date)
returns table as
return(
with Price_Selected_Time as (select * from Price where [date] between @from and @to)
select
    (select top 1([Open]) from Price_Selected_Time) as 'Open',
    (select top 1([Close]) from Price_Selected_Time order by date desc) as 'Close',
    [Close] - [Open] as 'Difference'
);

I feel this code is very clumsy. And it also won’t let me pass, because the ‘Open’and ‘Close’ is not defined yet.

Is there anyway to query this in one select?

Thank you

Answer

We can handle this via a regular query using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Date) rn_start,
        ROW_NUMBER() OVER (ORDER BY Date DESC) rn_end
    FROM Price
)

SELECT
    MAX(CASE WHEN rn_start = 1 THEN [Open] END) AS OpenStart,
    MAX(CASE WHEN rn_end = 1 THEN [Close] END) AS CloseEnd,
    MAX(CASE WHEN rn_end = 1 THEN [Close] END) -
        MAX(CASE WHEN rn_start = 1 THEN [Open] END) AS diff
FROM cte;