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.
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;