Help with my SQL Query [closed]

Question

Hello all I am trying to get timestamps from today and yesterday. The thing is my query is only returning rows for today - am I missing something? I normally dont use multiple conditions in a JOIN statement but my logic seems like it makes sense

I am using dateadd to minus 1 day but its still returning only aug 8 entries - see screenshot as well

SELECT O.subscriber_key, O.timestamp, O.data from  abandoned_browse_1643376986  O
JOIN ENT._Subscribers s ON O.subscriber_key = s.SubscriberKey
AND  
(
cast(O.timestamp As Date) = cast(GetDate() As Date)     
OR 
cast(DATEADD(day,-1,O.timestamp )As Date) = 
cast(DATEADD(day,-1, GetDate())  as DATE)
)

Answer 1

Did you try something like the following, using the WHERE clause ?

SELECT O.subscriber_key, O.timestamp, O.data
FROM abandoned_browse_1643376986 O
JOIN ENT._Subscribers S ON O.subscriber_key = S.SubscriberKey
WHERE 
    DATEDIFF(DAY, O.timestamp, GETDATE()) = 0
OR
    DATEDIFF(DAY, O.timestamp, GETDATE()) = 1

Orig DE :

Filtered DE with only today's and yesterday's records :

Answer 2

Here's how I generally select rows from yesterday:

SELECT 
  O.subscriber_key
, O.timestamp
, O.data 
from abandoned_browse_1643376986 O
JOIN ENT._Subscribers s ON O.subscriber_key = s.SubscriberKey
where 
o.timestamp >= convert(date, getDate()-1)
and o.timestamp < convert(date, getDate())

Convert is native to SQLServer and is allegedly faster. When converting to a date you're effectively resetting the time part of the timestamp to midnight. So the translation of my example above would be:

select rows after yesterday at midnight and include anything before midnight today

Using sargable where-clauses is a good thing performance-wise.