SQL - Calculated Column

Question

I have a table which shows me a stock ticker, asofdate, open, close. I have created a computed column called daily PL which is simply the daily close-open.

,(close-open) AS daily_pl

I cannot figure out the logic of using the {open} from the first asofdate and the {close} from the most recent asofdate. and the table will naturally grow, so the logic needs to be dynamic. see below:

Answer 1

You can use window functions. I think first_value() does what you want -- getting the first open price for the ticker:

select j.*,
       (close - first_value(open) over (partition by ticker order by asofdate) as daily_pl
from jandata j;

If you just want one row in the result set for each ticker, then you can use:

select distinct ticker,
       (first_value(close) over (partition by ticker order by asofdate desc) -
        first_value(open) over (partition by ticker order by asofdate)
       ) 
from jandata;