How to identify rows that occur within a specific time frame?

Question

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.

Table

visitID     patientid    visit_date  
1           23           1/12/2018
2           23           1/30/2018
3           23           4/20/2018
4           23           5/02/2018

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.

interim stage (visit 2 is removed, and analysis begins again)

visitID     patientid    visit_date  
1           23           1/12/2018
3           23           4/20/2018
4           23           5/02/2018

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.

Anticipated output

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

My attempt to solve this puzzle:

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

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.

Dataset:

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')

Gerick

Super good question. I am facing a very similar issue for overlapping dates where I would like the query to end after identifying this similar pattern – Delorsedelos

can you provide more examples...really painful to read :) – Upperclassman

Answer 1

I believe you have to do this with a recursive CTE:

with vt as (
      select vt.*, row_number() over (partition by patientid order by visit_date) as seqnum
      from visits_table vt
     ),
     cte as (
      select vt.visit, vt.patientid, vt.visit_date, vt.visit_date as first_visit_date, seqnum
      from vt
      where seqnum = 1
      union all
      select vt.visit, vt.patientid, vt.visit_date,
             (case when vt.visit_date > dateadd(day, 90, cte.first_visit_date) then vt.visit_date else cte.first_visit_date end),
             vt.seqnum
      from cte join
           vt
           on vt.seqnum = cte.seqnum + 1 and vt.patientid = cte.patientid
     )
select cte.visit, cte.patientid, cte.visit_date,
       (case when first_visit_date = visit_date then 0 else 1 end) as flag
from cte
order by cte.patientid, cte.visit_date;

Here is a db<>fiddle.

Pentarchy