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

Question

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!

score 1 · Accepted Answer · answered Sep 02 '20 at 21:13

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.

I had to play with this a bit because of the real data's complexity but it does work now (and I have to say, recursive CTEs are very interesting!) — RiSt, Sep 03 '20 at 17:11
If I may ask, what is the thought process that leads you to recursive CTE? Is it the fact that the later record is relative to a previous but not immediately preceding record (where LAG/LEAD could work)? — RiSt, Sep 03 '20 at 18:07
@RiSt: yes, that's the problem. We need to pick a record, retain it until the next valid record is met, then *reiterate*. That's not possible with window functions. — GMB, Sep 04 '20 at 00:53

score 0 · Answer 2 · answered Sep 02 '20 at 22:06

Here's another approach. Similar to GMB's this adds a row_number to the Visits table in a CTE but it also adds the lead date difference between VisitDtm's. Then it takes cumulative "sum over" of the date difference and divides by 15. When that quotient increases by a full integer, it represents a billable event in the data.

Something like this

;with lead_cte as (
    select v.*, row_number() over (partition by PatientId order by VisitDtm) rn,
           datediff(d, VisitDtm, lead(VisitDtm) over (partition by PatientId order by VisitDtm)) lead_dt_diff
    from Visits v),
cum_sum_cte as (
    select lc.*, sum(lead_dt_diff) over (partition by PatientId order by VisitDtm)/15 cum_dt_diff 
    from lead_cte),
min_billable_cte as (
    select PatientId, cum_dt_diff, min(rn) min_rn
    from cum_sum_cte
    group by PatientId, cum_dt_diff)
select lc.* 
from lead_cte lc
     join min_billable_cte mbc on lc.PatintId=mbc.PatientId
                                  and lc.rn=mbc.min_rn;

I would like to try this as well but because GMB was first I attempted that method and it worked. — RiSt, Sep 03 '20 at 17:12

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

2 Answers2