Join tables, but with a timestamp between ... a head breaker!

[Anonymous]

I have 2 tables which I want to join in the correct way ... sounds simple, but it's a head breaker for me at the moment.

Call:
LOAD Call_id
  ,Employee
  ,Call_date
FROM Call;

NAV_Call:
LOAD Nav_Callnr
    ,Employee
    ,start_datetime
    ,end_datetime
FROM NAV_Call;

The resulting table should be:

JoinedCall:
LOAD Call_id
    ,Employee
  ,Call_date
  ,Nav_Callnr
  ,start_datetime
  ,end_datetime
RESIDENT ...

The difficulty is that the Call_date should be between start_datetime and end_datetime of the other table.

But also take into account that the Employee should be the same!!!

There are options to join both tables with a simple join and than filtering out the 'wrong' joined items.

But this option is time-consuming and not suitable for our amount of data.

After that I first thought of using the IntervalMatch on the date fields, but then I don't have the check on the Employee ...

INNER JOIN (Call)
IntervalMatch (Call_date)
LOAD end_datetime
   ,start_datetime
RESIDENT NAV_Call; 

LEFT JOIN (Call)
LOAD * RESIDENT NAV_Call;

sample with data:

Call

Call_id	Employee	Call_date
1200	AFU	17-5-2015 10:34:12
1201	IWE	17-5-2015 10:35:56
1202	MEN	17-5-2015 10:36:03

NAV_Call

Nav_Callnr	Employee	start_datetime	end_datetime
5698	AFU	17-5-2015 10:33:24	17-5-2015 10:35:58
5699	PBR	17-5-2015 10:33:56	17-5-2015 10:34:06
5700	MEN	17-5-2015 10:35:02	17-5-2015 10:35:57
5701	IWE	17-5-2015 10:35:25	17-5-2015 10:35:59
5702	MEN	17-5-2015 10:35:59	17-5-2015 10:36:10
5703	AFU	17-5-2015 10:36:02	17-5-2015 10:36:25

the join should result in:

JoinedCall

Call_id	Employee	Call_date	Nav_callnr	start_datetime	end_datetime
1200	AFU	17-5-2015 10:34:12	5698	17-5-2015 10:33:24	17-5-2015 10:35:58
1201	IWE	17-5-2015 10:35:56	5701	17-5-2015 10:35:25	17-5-2015 10:35:59
1202	MEN	17-5-2015 10:36:03	5702	17-5-2015 10:35:59	17-5-2015 10:36:10

Has anyone got idea's of how to solve this??

Regards, Anita

[Gysbert_Wassenaar · Accepted solution]

Intervalmatch also has an extended syntax where you can add the key fields so you can match the intervals per employee.

IntervalMatch (Call_date, Employee) 

LOAD end_datetime 

        ,start_datetime    

        ,Employee

RESIDENT NAV_Call;  

[MarcoWedel]

Hi,

one minor change regarding the order of the start and end timestamps:

IntervalMatch (Call_date, Employee)

LOAD start_datetime

        ,end_datetime   

        ,Employee

RESIDENT NAV_Call;

hope this helps

regards

Marco

[Anonymous]

it's correct the datetime normally should be start, end ... but in my case I need it exactly the opposite 😉