title: BigQuery joing 2 tables based on 2 values
body: <p>Asking again as I had no luck before</p>
  
  <p>Is there a way to combine 2 tables by serial number and using a date range from one table and dates from the other?</p>
  
  <p>I have 2 tables: table.events contains event data for a tracker, and table.dates contains the operational date of a tracker. A tracker can be in operation multiple times and as such, has a UID every time it is turned on. E.g. Tracker A can have the UID A1, A2, A3 etc.</p>
  
  <p>How can I combine table.events with data such as</p>
  
  <pre><code>Date,Serial,Quality,
  12/01/2019,A,1,
  12/01/2019,B,2,
  13/01/2019,C,3,
  14/01/2019,A,4,
  15/01/2019,A,5,
  16/01/2019,B,6,
  17/01/2019,B,7,
  17/01/2019,C,8,
  17/01/2019,B,9
  </code></pre>
  
  <p>with table.dates</p>
  
  <pre><code>Start_Date,End_Date,Serial_id,
  15/01/2019,18/01/2019,A1,
  08/01/2019,14/01/2019,A2,
  10/02/2019,18/01/2019,B1,
  13/01/2019,16/01/2019,C1,
  17/02/2019,18/01/2019,C2
  </code></pre>
  
  <p>To give me an end result such as</p>
  
  <pre><code>Date,Serial,Serial_id,Quality,Start Date,End Date
  12/01/2019,A,A1,1,15/01/2019,18/01/2019
  12/01/2019,B,B1,2,10/02/2019,18/01/2019
  13/01/2019,C,C1,3,13/01/2019,16/01/2019
  14/01/2019,A,A1,4,15/01/2019,18/01/2019
  15/01/2019,A,A2,5,08/01/2019,14/01/2019
  16/01/2019,B,B1,6,10/02/2019,18/01/2019
  17/01/2019,B,B1,7,10/02/2019,18/01/2019
  17/01/2019,C,C2,8,17/02/2019,18/01/2019
  17/01/2019,B,B1,9,10/02/2019,18/01/2019
  </code></pre>
  
  <p>Any help would be much appreciated.</p>
  
  <p>ALSO:</p>
  
  <p>One date will contain more than one serial number, so I want to also join by serial number.</p>
  
  <p>E.g.</p>
  
  <p>I have trackers D1 and E1 which both were tracking on 23/01/2019. I have seperate entries in table.events for D and E so I will need to match by date range and by a substring of serial id.</p>