CODEWITHSUNDEEP
sqlpostgresqlcalculated-columns
TommyK
TommyK

Reputation: 5

SQL - Calculated Column

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:

enter image description here

Upvotes: 0

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 1

Related Questions