how to connect 2 many to many relationship tables

[Anonymous]

I have 2 tables. 
table 1:
emID    errandID  errandstarttime   errandendtime
001       AO1         20201202     20201203
001      AO2           20201202      20201204
001      AO3            20201213     20201214

table 2:

emID      callstart           callend

001         20201202     20201203

001         20201204     20201204

001        20201212       20201213

001         20201213      20201213

I need to add callstart and callend column from table 2 to table 1  based on the below conditions:
1)  emID are the same

2) callstart>=errandstart  and callend<= errandend

Normally one errand only includes one call, but there maybe exceptions. 

Please help how to make this out!  Thanks !
 

[Ashish_Mathur]

Hi,

If for a single row in table 1, there are multiple rows in table 2, then what result do you expect?  Take a representative example and show the expected result.

[Anonymous]

Thank you!
Then I would expect for:
emID    errandID  errandstarttime   errandendtime  callstart1 callend1  callstart2   callend2

[Ashish_Mathur]

Share some data and show the expected result.

[amitchandak · Accepted solution]

@Anonymous , Create two new columns like

In table 1

call Start =

maxx(filter(Table2, Table2[callstart]>=Table1[errandstart] && Table2[callend]<=  Table2[errandend]  && Table2[emID] =Table1[emID] ) , Table2[callstart]) 

call End=

maxx(filter(Table2, Table2[callstart]>=Table1[errandstart] && Table2[callend]<=  Table2[errandend]  && Table2[emID] =Table1[emID] ) , Table2[called])