Query to join DateTimestamp column with another Table with StartDate and EndDate

I have below table with 3 column. Lets say table1 with the following data

+---------+-----------------------+----------+
| ID      | E_Date                | TimeDiff |
+---------+-----------------------+----------+
| 7390709 | 2020-03-26 22:25:30   | 456      |
+---------+-----------------------+----------+
| 7390707 | 2020-03-26 16:25:30   | 3        |
+---------+-----------------------+----------+
| 7390706 | 2020-03-26 19:25:30   | 3        |
+---------+-----------------------+----------+
| 7390701 | 2020-03-26 16:23:53   | 512419   |
+---------+-----------------------+----------+
| 7390696 | 2020-03-26 14:33:08   | 111034   |
+---------+-----------------------+----------+
| 7390681 | 2020-03-20   15:25:30 | 2        |
+---------+-----------------------+----------+
| 7390680 | 2020-03-20 16:25:30   | 63867    |
+---------+-----------------------+----------+
| 7390679 | 2020-03-20   13:25:30 | 63867    |
+---------+-----------------------+----------+
| 7390675 | 2020-03-20 12:30:46   | 63867    |
+---------+-----------------------+----------+

Another table with 4 columns, lets say table2 with following data

+-----------------------+---------------------+--------+------+
| Start_Date            | End_Date            | Typ_ID | Diff |
+-----------------------+---------------------+--------+------+
| 2020-03-26 18:00:01   | 2020-03-26 21:00:00 | 3      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-26   13:40:01 | 2020-03-26 17:30:00 | 1      | 230  |
+-----------------------+---------------------+--------+------+
| 2020-03-26 10:30:01   | 2020-03-26 13:30:00 | 1      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-26   06:10:01 | 2020-03-26 10:00:00 | 1      | 230  |
+-----------------------+---------------------+--------+------+
| 2020-03-20 18:00:01   | 2020-03-20 21:00:00 | 1      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-20   13:40:01 | 2020-03-20 17:30:00 | 3      | 230  |
+-----------------------+---------------------+--------+------+
| 2020-03-20 10:30:01   | 2020-03-20 13:30:00 | 1      | 180  |
+-----------------------+---------------------+--------+------+
| 2020-03-20   06:10:01 | 2020-03-20 10:00:00 | 1      | 230  |
+-----------------------+---------------------+--------+------+

What I would like to achieve is check in Table2 for start_date and End_Date where typ_Id = 1 from Table1 where E_Date comes under in Table2 Start_Date and End_Date.

for ID 7390706, 7390681,7390680 the E_Date comes under the range in table2 Start_Date and End_Date where Typ_ID=3 and for ID 7390709 there is no Start_Date and End_Date exist so those rows has be excluded from the final result.

So the final table has to be like this.

+---------+-----------------------+----------+
| ID      | E_Date                | TimeDiff |
+---------+-----------------------+----------+
| 7390707 | 2020-03-26 16:25:30   | 3        |
+---------+-----------------------+----------+
| 7390701 | 2020-03-26 16:23:53   | 512419   |
+---------+-----------------------+----------+
| 7390696 | 2020-03-26 14:33:08   | 111034   |
+---------+-----------------------+----------+
| 7390679 | 2020-03-20   13:25:30 | 63867    |
+---------+-----------------------+----------+
| 7390675 | 2020-03-20 12:30:46   | 63867    |
+---------+-----------------------+----------+

I am still learning joins so any help will be appreciated.

http://sqlfiddle.com/#!18/6eb43

Answer

Try the following and here is the demo.

select
    id, 
    e_date,
    timediff
from tableA
join tableB 
on e_date between Start_Date and End_Date
where typ_id = 1

output:

| id      | e_date                   | timediff |
| ------- | ------------------------ | -------- |
| 7390707 | 2020-03-26 16:25:30      | 3        |
| 7390701 | 2020-03-26 16:23:53      | 512419   |
| 7390696 | 2020-03-26 14:33:08      | 111034   |
| 7390679 | 2020-03-20 13:25:30      | 63867    |
| 7390675 | 2020-03-20 12:30:46      | 63867    |

Advertisement

Answer