How to capture first row in a grouping and subsequent rows that are each a minimum of 15 days apart?

Assume a given insurance will only pay for the same patient visiting the same doctor once in 15 days. If the patient comes once, twice, or twenty times within those 15 days to the doctor, the doctor will get only one payment. If the patient comes again on Day 16 or Day 18 or Day 29 (or all three!), the doctor will get a second payment. The first visit (or first after the 15 day interval) is always the one that must be billed, along with its complaint.

The SQL for all visits can be loosely expressed as follows:

SELECT  VisitID
       ,PatientID
       ,VisitDtm
       ,DoctorID
       ,ComplaintCode
FROM Visits

The goal is to query the Visits table in a way that would capture only billable incidents.

I have been trying to work through this question which is in essence quite similar to Group rows with that are less than 15 days apart and assign min/max date. However, the reason this won’t work for me is that, as the accepted answerer (Salman A) points out, Note that this could group much longer date ranges together e.g. 01-01, 01-11, 01-21, 02-01 and 02-11 will be grouped together although the first and last dates are more than 15 days apart. This presents a problem for me as it is a requirement to always capture the next incident after 15 days have passed from the first incident.

I have spent quite a few hours thinking this through and poring over like problems, and am looking for help in understanding the path to a solution, not necessarily an actual code solution. If it’s easier to answer in the context of a code solution, that is fine. Any and all guidance is very much appreciated!

Answer

This type of task requres a iterative process so you can keep track of the last billable visit. One approach is a recursive cte.

You would typically enumerate the visits of each patient use row_number(), then traverse the dataset starting from the first visit, while keeping track of the last “billable” visit. Once a visit is met that is more than 15 days latter than the last billable visit, the value resets.

with 
    data as (
        select visitid, patientid, visitdtm, doctorid,
            row_number() over(partition by patientid order by visitdtm) rn
        from visits
    ),
    cte as (
        select d.*, visitdtm as billabledtm from data d where rn = 1
        union all
        select d.*, 
            case when d.visitdtm >= dateadd(day, 15, c.billabledtm)
                then d.visitdtm
                else c.billabledtm
            end
        from cte c
        inner join data d 
            on d.patientid = c.patientid and d.rn = c.rn + 1
    )
select * from cte where visitdtm = billabledtm order by patientid, rn

If a patient may have more than 100 visits, then you need to add option (maxrecursion 0) at the very end of the query.