title: How to identify rows that occur within a specific time frame?
body: <p>I have a table that contains hospital visits for patients. I am trying to flag visits that occur within 90 days of the previous visit. However, the caveat to this is that once a visit is flagged as an overlap visit, that visit should not be used to assess an overlap with another visit. Let me explain with an example.</p>
  <h3>Table</h3>
  <pre><code>visitID     patientid    visit_date  
  1           23           1/12/2018
  2           23           1/30/2018
  3           23           4/20/2018
  4           23           5/02/2018
  </code></pre>
  <p>In the example above, the patient had 4 visits. Visit 2 was within 90 days of visit 1, so visit 2 should be flagged as an overlap. Once visit 2 is flagged, that row should not be used in the analysis for overlap identification for any future visits. Conceptually, it would be like removing visit 2 and beginning the analysis again.</p>
  <p>interim stage (visit 2 is removed, and analysis begins again)</p>
  <pre><code>visitID     patientid    visit_date  
  1           23           1/12/2018
  3           23           4/20/2018
  4           23           5/02/2018
  </code></pre>
  <p>So even though visit 3 overlaps with visit 2, since visit 2 has been removed from the analysis, visit 3 will not be flagged as the previous visit (visit 1) is farther than 90 days away. Lastly, the 4 visit should be flagged as it overlaps with a visit that was not flagged.</p>
  <h3>Anticipated output</h3>
  <pre><code>visitID     patientid    visit_date flag
  1           23           1/12/2018  0
  2           23           1/30/2018  1
  3           23           4/20/2018  0 
  4           23           5/02/2018  1
  </code></pre>
  <p>My attempt to solve this puzzle:</p>
  <pre><code>WITH overlaps AS 
      (SELECT DISTINCT T2.visit
      FROM visits_table AS T1
      INNER JOIN visits_table AS T2
              ON T1.visit != T2.visit
                  AND T2.visit_date BETWEEN T1.visit_date AND DATEADD(DAY, 89, T1.visit_date))
      
      SELECT T3.visit, T3.patientid, T3.visit_date,
      CASE WHEN EXISTS (SELECT 1 FROM overlaps
                          WHERE overlaps.visit = T3.visit) 
                  THEN 1
                  ELSE 0
                  END flag
      FROM visits_table T3
  </code></pre>
  <p>What my code is doing is checking each row regardless of whether it should be used in the analysis. I don't know how to make it dynamic so that it ignores rows that should be ignored.</p>
  <h3>Dataset:</h3>
  <pre><code>create table visits_table (visit int,patientid int,visit_date date);
  
  INSERT INTO visits_table (visit, patientid, visit_date) VALUES (1,23,'1/12/2018')
  INSERT INTO visits_table (visit, patientid, visit_date) VALUES (2,23,'1/30/2018')
  INSERT INTO visits_table (visit, patientid, visit_date) VALUES (3,23,'4/20/2018')
  INSERT INTO visits_table (visit, patientid, visit_date) VALUES (4,23,'5/02/2018')
  </code></pre>