CODEWITHSUNDEEP
sqldatetimedata-analysisdate-rangepresto
Chris90
Chris90

Reputation: 1998

SQL Query to filter if a date column in one table does not exist in a date range in joined table?

I have two tables that look like below:

Table 1:

ID  |  Timestamp
231   3/27/19 0:46
345   3/22/19 22:46

Table 2:

ID  |  Start Time   |  End Time
231   3/27/19 0:00    3/27/19 0:58
345   3/22/19 22:50   3/22/19 23:50

Is there a good method in which I only want all rows in Table 1 that do not exist in any start and end time range in any rows in Table 2?

So any rows in table one where that timestamp is not in the timeframe or between, that same ids start and end time in table 2?

Thanks for the time!

Upvotes: 0

Views: 419

Answers (1)

Ctznkane525
Ctznkane525

Reputation: 7465

Something like this?

SELECT * 
FROM TABLE1 
WHERE NOT EXISTS (SELECT NULL FROM TABLE2 
                  WHERE TABLE1.ID = TABLE2.ID 
                    AND TABLE1.TIMESTAMP >= TABLE2.START_DATE 
                    AND TABLE1.TIMESTAMP <= TABLE2.END_DATE)

I named your columns START_DATE and END_DATE. How spaces are handled diff between each DB.

Upvotes: 1

Related Questions