SQL Add Medication days and find gaps

Question

I need add the days of medication for people over the course of a year and determine how many days were covered by medication.

This is similar to a Gaps and Islands problem except that people don't wait till the last day of their previous prescription before getting a new one so the days' supply needs to be added from the initial dispense date to determine a date range. To further complicate it, they are allowed a 7-day gap of no medication to still be considered covered.

The measure is met when the member adheres to OUD pharmacotherapy for 180 days or more without a gap in treatment of more than eight days

The closest I got was using Preceding and but it only added the days for the ones around it not the entire group. I need to add all the Days supply of medication to the first Date of Service for a person in which the DOS is covered by the medication range.

;WITH TBL AS (
    SELECT CAST('2022-01-24' AS DATE) AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-02-12' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-03-01' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-04-01' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-05-12' AS DOS, 60 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-07-02' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-08-08' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION  
    SELECT '2022-09-24' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION  
    SELECT '2022-10-21' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION  
    SELECT '2022-11-22' AS DOS, 30 AS DAYS, 'John' F_NAME
    UNION 
    SELECT '2022-02-16' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-03-11' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-04-30' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-05-22' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-06-10' AS DOS, 60 AS DAYS, 'Mary' F_NAME
    UNION 
    SELECT '2022-08-20' AS DOS, 60 AS DAYS, 'Mary' F_NAME
    UNION  
    SELECT '2022-09-24' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION  
    SELECT '2022-10-21' AS DOS, 30 AS DAYS, 'Mary' F_NAME
    UNION  
    SELECT '2022-12-10' AS DOS, 30 AS DAYS, 'Mary' F_NAME
)


SELECT F_NAME, MIN(DOS), MAX(EDOS) , DATEADD(DAY, SUM(DAYS), MIN(DOS))
FROM ( 
SELECT F_NAME, DOS, EDOS, DAYS, SUM(ADD1) OVER(PARTITION BY F_NAME ORDER BY DOS,EDOS ROWS UNBOUNDED PRECEDING) AS GRP 
    FROM ( SELECT *, DATEADD(DAY, DAYS, DOS) AS EDOS,   
            IIF(DOS <= MAX(DATEADD(DAY, DAYS, DOS))OVER(PARTITION BY F_NAME ORDER BY DOS, DATEADD(DAY, DAYS, DOS) ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0, 1) AS ADD1 FROM TBL ) AS A 
            ) AS G 
            GROUP BY F_NAME, GRP

In the example data...

DOS	DAYS	F_NAME
2022-01-24	30	John
2022-02-12	30	John
2022-03-01	30	John
2022-04-01	30	John
2022-05-12	60	John
2022-07-02	30	John
2022-08-08	30	John
2022-09-24	30	John
2022-10-21	30	John
2022-11-22	30	John
2022-02-16	30	Mary
2022-03-11	30	Mary
2022-04-30	30	Mary
2022-05-22	30	Mary
2022-06-10	60	Mary
2022-08-20	60	Mary
2022-09-24	30	Mary
2022-10-21	30	Mary
2022-12-10	30	Mary

... there should only be one range for John (2022-01-24 - 2022-12-20) since he was covered all year. He did have a gap from 2022-09-22 - 2022-09-23 and 2022-11-21 but they would be covered by the 7-day exception. Mary would have two islands - 2022-02-16 to 2022-04-17, 2022-04-30 to 2022-06-12 (since the 2022-08-29 - 2022-09-02 gap is less than 7 days).

Any help getting any closer would be appreciated. I've looked but haven't found similar questions where the total days need to be added to the initial date.

I'm using SQL server 2019.

Seems to be something lacking in the description because I have no idea where you are pulling the dates from in the desired results — Martin Smith, Jul 14 '23 at 23:48

shawnt00 · Accepted Answer · 2023-07-16T05:16:55.273

This technique requires running a loop to detect when the next gap occurs per person. Knowing where a gap occurs (or starting at the beginning) you can then reset with a new pass resuming where it left off. A work table (likely a SQL Server temp table) is created to accumulate the islands in this format:

create table results (
    F_NAME varchar(32) not null,
    min_DOS date not null, max_DOS date not null,
    supply_end date not null, gap_end date null, gap_length int null);

The loop looks like this:

declare @r int = 1;
while @r > 0 begin

with data as (
    select *,
        min(DOS)  over (partition by F_NAME) as min_DOS,
        sum(DAYS) over (partition by F_NAME order by DOS) - DAYS as cum_DAYS,
        lag(DOS)  over (partition by F_NAME order by DOS) as prev_DOS,
        lead(DOS) over (partition by F_NAME order by DOS) as next_DOS
    from T t
    where not exists (
        select 1 from results as r
        where t.F_NAME = r.F_NAME and t.DOS <= r.max_DOS
    )
), series as (
    select *,
        case when supply_end < DOS or next_DOS is null then 1 end as end_series,
        case when datediff(day, supply_end, DOS) > 7
             then datediff(day, supply_end, DOS) end as gap_length
    from data cross apply (values (dateadd(day, cum_DAYS, min_DOS))) v1(supply_end)
              cross apply (values (dateadd(day, DAYS, supply_end)))  v2(next_end)
), islands as (
    select *,
        row_number() over (partition by F_NAME order by DOS) as rn
    from series
    where end_series = 1 and (next_DOS is null or gap_length is not null)
)
insert into results (F_NAME, min_DOS, max_DOS, supply_end, gap_end, gap_length)
select
    F_NAME,
    min_DOS,
    case when gap_length is not null then prev_DOS else DOS end,
    case when gap_length is not null then supply_end else next_end end,
    case when gap_length is not null then DOS end,
    case when gap_length is not null then gap_length
         when next_end < getdate() then datediff(day, next_end, getdate()) end
from islands
where rn = 1;
set @r = @@rowcount;

end

And the final output could look like this:

select F_NAME, min_DOS, max_DOS, supply_end,
    datediff(day, min_DOS,
        case when supply_end < getdate()
             then supply_end else getdate() end) as treatment_days,
    gap_end, gap_length,
    case when gap_end is not null then 'resumed supply following protocol gap'
         when supply_end >= getdate() then 'ongoing supply'
         when gap_length <= 7 then 'ongoing gap within tolerance'
         else 'protocol gap'
    end status
from results
order by F_NAME, min_DOS;

https://dbfiddle.uk/9RUUfKxJ

Thanks, this seems to be working well with a larger dataset. — Hannover Fist, Jul 20 '23 at 19:03

Merlin Nestler · Answer 2 · 2023-07-15T01:48:47.443

Edit: I think you can't use my example for your case. I made the assumption that on the DOS date you go to the docter, get one pill or shot and then you are fine for X days. However, the DOS is probably the amount of pills you get, right? So when you get new ones after 25 days when having a 30 day pack you still have 5 in reserve when you get the next 30.

Given the below code:

The attribute Max_Medication_Streak will give you the maximum amount of time a patient was covered by medication without a gap bigger than DOS + 7
The attribute Days_Covered_By_Medicine gives you the sum of all days that were covered by meditication considering the whole dataset
The attribute Timeframes_Are_Overlapping can be used as a filter to identify all cases where where the End of the last medication is before the start of the next medication (essentially showing you when somebody messed up with taking their medication in time and therefore breaking their streak).

Code:

Select *
into #data
From 
    (
          SELECT CAST('2022-01-24' AS DATE) AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION 
          SELECT '2022-02-12' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION 
          SELECT '2022-03-01' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION 
          SELECT '2022-04-01' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION 
          SELECT '2022-05-12' AS DOS, 60 AS DAYS, 'John' F_NAME
          UNION 
          SELECT '2022-07-02' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION 
          SELECT '2022-08-08' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION  
          SELECT '2022-09-24' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION  
          SELECT '2022-10-21' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION  
          SELECT '2022-11-22' AS DOS, 30 AS DAYS, 'John' F_NAME
          UNION 
          SELECT '2022-02-16' AS DOS, 30 AS DAYS, 'Mary' F_NAME
          UNION 
          SELECT '2022-03-11' AS DOS, 30 AS DAYS, 'Mary' F_NAME
          UNION 
          SELECT '2022-04-30' AS DOS, 30 AS DAYS, 'Mary' F_NAME
          UNION 
          SELECT '2022-05-22' AS DOS, 30 AS DAYS, 'Mary' F_NAME
          UNION 
          SELECT '2022-06-10' AS DOS, 60 AS DAYS, 'Mary' F_NAME
          UNION 
          SELECT '2022-08-20' AS DOS, 60 AS DAYS, 'Mary' F_NAME
          UNION  
          SELECT '2022-09-24' AS DOS, 30 AS DAYS, 'Mary' F_NAME
          UNION  
          SELECT '2022-10-21' AS DOS, 30 AS DAYS, 'Mary' F_NAME
          UNION  
          SELECT '2022-12-10' AS DOS, 30 AS DAYS, 'Mary' F_NAME
      ) as t1

Select 
F_Name
, DOS as Medication_Start
, Dateadd(dd, DAYS+7, DOS) as Medication_End
, Isnull(
  lead(DOS) over (partition by F_Name order by DOS) 
  , Dateadd(dd, DAYS+7, DOS)
) as Next_Medication_Start
, IIF(DOS = max(DOS) over (partition by F_Name),'Yes','No') as Most_Recent_Medication_Date
into #timeframes
From #data

Select *
, Cast(
  Datediff(
    dd
    , Medication_Start
    , IIF(Next_Medication_Start<Medication_End,Next_Medication_Start,Medication_End)
  )
  as Integer
) as Days_Covered_By_Medicine
into #days_covered
from #timeframes

Select 
*
, IIF(Medication_End>=Next_Medication_Start,'Yes','No') as Timeframes_Are_Overlapping
into #overlapping
From #days_covered

Select *
, (Select min(Medication_End) 
   From #overlapping ol2
   where 1=1
   and (ol2.F_Name = ol1.F_Name
   and ol2.Timeframes_Are_Overlapping = 'No'
   and ol2.Medication_End > ol1.Medication_Start) 
   Or (ol2.Most_Recent_Medication_Date = 'Yes'
       and ol2.F_Name = ol1.F_Name
       and ol2.Medication_End > ol1.Medication_Start)
   ) as Last_Medication_Covered_Date_In_Streak
into #streak_timeframes
From #overlapping as ol1

Select 
F_Name
, max(
  Datediff(
    dd
    , Medication_Start
    , Last_Medication_Covered_Date_In_Streak
  )
) as Max_Medication_Streak
From #streak_timeframes
Group by F_Name

Select 
F_Name
, sum(Days_Covered_By_Medicine) as Days_Covered_By_Medicine
From #days_covered
Group by F_Name

Yes - the DAYS is the number of Days Supply that is given on the Date of Service (DOS). The overlapping days of pills is what's been giving me issues. — Hannover Fist, Jul 17 '23 at 16:58
I think apart from looping over your data this is not solvable. Have you ever worked with while loops in SQL? I don't have the time to write the code but here is what I would do: Give every person a row number ordered by date to have an index for the loop. With max over that new column you can get the repeats your loop must do. Initiate the loop with a table where the new column is 1. Then calculate the time between the 1 rows and the 2 rows. Subtract that from the last supply and add the rest (if positive) to the supply received on the second date. — Merlin Nestler, Jul 18 '23 at 05:47
If the difference is negative, don't add it to the second supply. Instead check whether the difference is smaller than -7 (grace period of 7 days). If it is smaller you found a case where someone didn't take his/her medication properly. You can mark the line accordingly and if needed calculate the timeframe. You can also add a column for counting the days within the current streak. If the difference is smaller than -7 you can reset this counter. In the end that allows you to filter for the longest period someone took medication properly. — Merlin Nestler, Jul 18 '23 at 05:48

SQL Add Medication days and find gaps

2 Answers2