This content has been marked as final. Show 3 replies
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:
|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:
|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??