Search code examples
sqlstockportfolio

Limit query result once value has been reached

For my Stock portfolio I have two tables:

  1. The Date, Open, Close, High, Low for all stocks in my portfolio.
  2. The stocks I've picked, along with a target price for the stock to hit. Ticker, Target

So my query result looks something like this:

enter image description here

The O,C,H,L data comes from an API feed, I would like my query result to stop reporting on a ticker (in this case ALGN), once the value in the "high" column exceeds the value in the "target" column.

If not, the query result will continue to show me O,C,H,L data for a stock I have already sold and is no longer in my portfolio.

Would I use a CASE expression?

23/08/2021 22:46

I have added in a CASE expression that shows me the date my trade hit the desired target, however, my query result continues to show data after the target has been hit.

enter image description here

ideally after 2021-02-04, my query would stop giving me data for that ticker....

Solution

  • One method uses window functions:

    select t.*
from (select t.*,
             min(case when high > target then date end) over (partition by ticker) as first_target_date
      from t
     ) t
where date >= first_target_date or
      first_target_date is null;