3 Replies Latest reply: May 19, 2015 5:16 AM by Anita Fuchten RSS

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

    Anita Fuchten

      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_idEmployeeCall_date
      1200AFU17-5-2015 10:34:12
      1201IWE17-5-2015 10:35:56
      1202MEN17-5-2015 10:36:03

      NAV_Call

      Nav_CallnrEmployeestart_datetimeend_datetime
      5698AFU17-5-2015 10:33:2417-5-2015 10:35:58
      5699PBR17-5-2015 10:33:5617-5-2015 10:34:06
      5700MEN17-5-2015 10:35:0217-5-2015 10:35:57
      5701IWE17-5-2015 10:35:2517-5-2015 10:35:59
      5702MEN17-5-2015 10:35:5917-5-2015 10:36:10
      5703AFU17-5-2015 10:36:0217-5-2015 10:36:25

       

      the join should result in:

       

      JoinedCall

      Call_idEmployeeCall_dateNav_callnrstart_datetimeend_datetime
      1200AFU17-5-2015 10:34:12569817-5-2015 10:33:2417-5-2015 10:35:58
      1201IWE17-5-2015 10:35:56570117-5-2015 10:35:2517-5-2015 10:35:59
      1202MEN17-5-2015 10:36:03570217-5-2015 10:35:5917-5-2015 10:36:10

       

       

       

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

       

      Regards, Anita