title: Match records and perform date calculations within same query?
body: <p>This referes to a question i asked yesterday which didn't get solved. I have now simplified my tables so the question can be answered, but need some further help.</p>
  
  <p>I have a table called chd18 (100,000+ records), which contains records of 18+ patients with a disease CHD.</p>
  
  <pre><code>ID|Age|indexdate
  1  20  11/05/2013
  2  25  19/10/2012
  3  44  07/12/2013
  4  85  01/01/2014
  </code></pre>
  
  <p>I then have another table (died1216) with all patients (500,000+ records) that have died between April 2012-March 2016</p>
  
  <pre><code>ID|Age|deathdate
  2  25  20/10/2012
  4  85  03/01/2015
  </code></pre>
  
  <p>What I want to do is create another table where I check if any of the CHD18 patients have died between April 2012-March 2016 (table died1216), if so, is the difference between indexdate and deathdate >=12  months.</p>
  
  <p>Essentially that table should look like this</p>
  
  <pre><code>ID|Age|indexdate|deathdate
  4  85  01/01/2014 03/01/2015
  </code></pre>
  
  <p>I know a join between these 2 tables are needed on the ID, but not sure how to achieve this, especially checking if 12months has passed between the 2 dates.</p>
  
  <p>Please can someone kindly assist.</p>
  
  <p><strong>For anyone saying the answer is in my previous question, can you kindly explain how the indexdate and deathdate are being compared for 12months or more? because i see it being compared to currentdate - interval '1 year'??</strong></p>
  
  <p>Thanks</p>