title: SQLite function to combine tables with min() and max() function with dates?
body: <p>In SQLite, I am trying to combine both tables. Specifically, I am trying to find a way to combine lab result dates with 0-7 days of follow-up for diagnosis dates (minimum 0 day, like the same day, to maximum 7 days). I have attached the tables here (note: not real ID, ENCID, lab result date, and diag_date numbers). Is there a possible way to combine both tables without the first row (of Table 1) attached to DIAG_DATE of 11/19/2020 in SQLite? If not, what about in Python?</p>
  <p>Table 1</p>
  <pre><code>ID  ENCID     LAB RESULT DATE 
  1      098    10/29/2020
  1      098    11/17/2020    
  1      098    11/15/2020
  1      098    11/12/2020
  1      098    11/19/2020
  </code></pre>
  <p>Table 2</p>
  <pre><code>ID  ENCID      DIAG_DATE
  1     098      11/19/2020
  1     098      10/01/2021
  </code></pre>
  <p>My goal:
  Table 3</p>
  <pre><code>ID   ENCID    LAB_RESULT_DATE     DIAG_DATE
  1      098    11/12/2020          11/19/2020
  1      098    11/15/2020          11/19/2020 
  1      098    11/17/2020          11/19/2020
  1      098    11/19/2020          11/19/2020
  </code></pre>
  <p>Here is my SQLite code below (I am aware this is not right):</p>
  <pre><code>CREATE TABLE table3 AS
  SELECT *
  FROM table1
  JOIN table2
  WHERE table1.ID=table2.ID AND table1.ENCID=table2.ENCID AND DIAG_DATE &gt;= LAB_RESULT_DATE
  HAVING MAX(DIAG_DATE)&gt;MIN(LAB_RESULT_DATE)
  ORDER BY table1.ID ASC
  </code></pre>