I have two tables like these:

Table1

Table2

I want to join the two tables based on their timestamp. So if the table1.timestamp is between table2.Begin_timestamp and table2.End_timestamp, it would add "isFound=1". For example, the first row of Table1 is 16:16:50 and is between 16:14:00 and 16:17:02 (the first row of Table2). I want a table like this:

Wanted table

I tried the following but it only works for the last row.

SELECT t1.timestamp, 
       t1.value,
       (CASE WHEN (t1.Timestamp BETWEEN t2.Begin_Timestamp AND t2.End_Timestamp)
             THEN '1'
        ELSE '0') AS isFound
FROM Table1 t1
LEFT JOIN (
   SELECT * 
   FROM Table2
) AS t2
ON t1.Timestamp BETWEEN t2.Begin_Timestamp AND t2.End_Timestamp 
)

RESULT :

I use MS-SQL server. Can someone help me with that? I feel like this it is easy but I don't find the problem. Thanks!

CodePudding user response：

You can use case expression combined with an exists check:

select *,
    case when exists (
      select * from t2 
      where t1.Timestamp between t2.Begin_Timestamp and t2.End_Timestamp
    ) then 1 else 0 end IsFound
from t1;

CodePudding user response：

You can do a LEFT JOIN between the two tables on the conditions that the "Table1.Timestamp" is found between the two "Table2" timestamps.

Then you can exploit the non-matching rows having both "Begin_Timestamp" and "End_Timestamp" equal to NULL inside your CASE statement for the creation of the "isFound" field.

SELECT table1.*,
       CASE WHEN [Begin_Timestamp] IS NULL THEN 0 ELSE 1 END AS isFound 
FROM      table1
LEFT JOIN table2
       ON table2.[Begin_Timestamp] < table1.[Timestamp] 
      AND table2.[End_Timestamp] > table1.[Timestamp] 

Check the demo here.

Note: this solution assumes that all your "table2" timestamp periods are disjoint among each other, otherwise you need to add the DISTINCT keyword right after the SELECT keyword.