Calculating Difference Betwen Two Dates where Gaps Exist

Question

I need to track for an outpatient unit how long a patient participated in a certain outpatient treatment cycle, that in theory should be everyday. Typically I could just do a min and a max on appointment date. However, if a patient has missed a week of treatments then I have to start the day count again. I am having issues trying to account for that time gap.

For example: Patient A attended treatment everyday from 1/1-1/12, she then took a break and did not return until 1/20 and attended treatments on 1/20, 1/23, 1/26, and 1/29. So I should have two day counts one for 12 days and one for 10 days.

Answer 1

If you have a column with a patient id and date, then you can group the dates into "islands" of adjacency. If your rule is one week of difference, then you can use lag() and cumulative sums:

  select t.*,
         sum(startflag) over (partition by patientid order by date) as episode
  from (select t.*,
               (case when lag(date) over (partition by patientid order by date) < dateadd(day, -7, date)
                     then 1 else 0
                end) as startflag
        from t
       ) t

If you want a count of dates for each episode of treatment:

select patientid, min(date), max(date), count(*) as numtreatments
from (select t.*,
             sum(startflag) over (partition by patientid order by date) as episode
      from (select t.*,
                   (case when lag(date) over (partition by patientid order by date) < dateadd(day, -7, date)
                         then 1 else 0
                    end) as startflag
            from t
           ) t
      ) t
group by patientid, episode;