How to query two values in different columns from different dates?

Question

I'm trying to query a daily_price table by the following logic:

today's close > today's open and today's close > yesterdays high

The desired output is returning all rows from today that meet the criteria. With the below example, and assuming today is 2022-08-31, the query would return ACR but not NBI because the logic matches (i.e. 2022-08-31 close > 2022-08-31 open and 2022-08-31 close > 2022-08-30 high).

daily_price example:

symbol	date	open	high	low	close
ACR	2022-08-30	0.061	0.063	0.06	0.06
ACR	2022-08-31	0.066	0.07	0.066	0.07
NBI	2022-08-30	1.52	1.52	1.51	1.52
NBI	2022-08-31	1.51	1.52	1.505	1.515

I tried this query with no luck:

SELECT * 
FROM daily_price 
WHERE close > open
AND date = '2022-08-31' 
AND (SELECT close FROM daily_price WHERE date = '2022-08-31') > (SELECT high FROM daily_price WHERE date = '2022-08-30')

DannySlor · Accepted Answer · 2022-09-05 12:31:36Z

1

I'm using lag to compare today's close with yesterday's high and filtering by date.

select  symbol  
       ,date    
       ,open    
       ,high    
       ,low 
       ,close
from    (
        select *
              ,lag(high)    over (partition by symbol order by date)      as pre
        from   t
        ) t

where   close > pre 
  and   close > open 
  and   date = '2022-08-31'

symbol	date	open	high	low	close
ACR	2022-08-31	0.066	0.07	0.066	0.07

Fiddle

edited Sep 5, 2022 at 12:31

answered Sep 5, 2022 at 2:51

DannySlor

4,6201 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ahmed · Accepted Answer · 2022-09-05 03:40:13Z

0

You may use Exists with a correlated subquery as the following:

Select T.symbol, T.date_, T.open, T.high, T.low, T.close 
From daily_price T
Where T.close > T.open And
Exists (Select 1 From daily_price D 
        Where D.symbol=T.symbol And 
              D.date_=Date(T.date_, '-1 day') And 
              D.high < T.close)

See a demo db<>fiddle.

answered Sep 5, 2022 at 3:40

ahmed

9,2013 gold badges11 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to query two values in different columns from different dates?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related