Join with overlapping times using sql?-CodePudding

I have 2 tables, TABLE1:

job     job_type  job_id    start_time      end_time
hello   EXPRESS   125445801 6/9/22 10:59    6/9/22 11:59
hello   EXPRESS   125449030 6/9/22 11:19    6/9/22 11:38
hello   EXPRESS   125444620 6/9/22 10:59    6/9/22 11:11

and a TABLE2:

job     time_sample
hello   6/9/22 10:59
hello   6/9/22 11:13
hello   6/9/22 11:21
hello   6/9/22 11:49

The issue is on the first table. Due to the overlap of start and end times, I need to be able to distinguish between whether there is an overlap. If there is an overlap, then I need to identify the category as "MULTIPLE". If there isn't an overlap, then I only flag the category as. the job_type. When joining to second table, the second table needs to have the job_type populated such that it will either show the one job_type if the time_sample from TABLE2 only falls between the start and end times of only 1 record from TABLE1. if the time_sample falls within more than 1 start/end times for that one job, then it should populate MULTIPLE. I'm having a hard time understanding how to be able to perform this kind of semi-aggregation/join logic to create the following:

job     time_sample     job_type
hello   6/9/22 10:59    MULTIPLE
hello   6/9/22 11:13    EXPRESS
hello   6/9/22 11:21    MULTIPLE
hello   6/9/22 11:49    EXPRESS

The join should be on 'job' and where TABLE2.time_sample is between TABLE1.start_time and TABLE1.end_time

CodePudding user response：

You can do a simple join between the tables. For the job type check if there are multiple matches:

select 
  t2.job, 
  t2.time_sample, 
  if (count(*)>1, 'MULTIPLE', max(t1.job_type)) as "job_type"
from table2 t2
  join table1 t1 on t2.time_sample between t1.start_time and t1.end_time
group by t2.job, t2.time_sample

See db-fiddle