Calculate overlapping days between 2 or more drugs

1

I am trying to calculate the overlapping days between 2 drugs to get an accurate reflection of days supply. I don't even know if this is possible but I figured I'd ask.

Here is an example of what we need to be able to do if Drug A and Drug B are in the same drug class:

Drug A claim dates: 8/01/17 X 30 day supply
9/01/17 X 30 day supply
10/01/17 X 30 day supply

Drug B claim dates: 9/5/17 X 30 day supply
10/5/17 X 30 day supply

Adding Drug A + Drug B claims would = 150 days, but that is not a correct reflection of days they are receiving therapy with drugs in the same drug class.

Accurate reflection would be: (Drug A 90 days + Drug B 60 days) minus 55 days overlap where patient is receiving both Drug A + Drug B, so total days of therapy is more accurately 95 days.

For example: Drug A QUETIAPINE with service dates of 8/28/17, 10/4/17, 10/29/17 and drug B Drug NUPLPAZID with service dates of 20170808
20170822
20170909
20170924
20171009
20171026
20171113
Both drugs have days supply of 14 days each fill.
Does anyone have any ideas on how to do this? Thank you!

2

Below is my proposed solution (at least as long as the data is not too extremely large). Naturally you could drop drug class from the query and instead just provide a specific list of drugs, as in the commented-out WHERE clauses.
Immediately after the final query is sample data in a useable form. In the future, it's extraordinarily useful to everyone trying to help to post sample data in such a readily useable format.
Btw, I think the first total days are 94 not 95: because Oct has 31 days, 30 days from 10/5 is 11/3 rather than 11/4.

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
    CROSS JOIN cteTally10 c3
)
SELECT DISTINCT dc.drug_class, DATEADD(DAY, t.number, dc.start_date) AS drug_date
FROM #drug_claims dc
INNER JOIN cteTally1000 t ON t.number BETWEEN 0 AND dc.duration - 1
--WHERE drug_name IN ('A', 'B')
--WHERE drug_name IN ('QUETIAPINE', 'NUPLPAZID')
ORDER BY dc.drug_class, drug_date

CREATE TABLE #drug_claims (
    drug_name varchar(30) NOT NULL,
    drug_class smallint NOT NULL,
    start_date date NOT NULL,
    duration int NOT NULL
    )
INSERT INTO #drug_claims VALUES('A', 1, '20170801', 30)
INSERT INTO #drug_claims VALUES('A', 1, '20170901', 30)
INSERT INTO #drug_claims VALUES('A', 1, '20171001', 30)
INSERT INTO #drug_claims VALUES('B', 1, '20170905', 30)
INSERT INTO #drug_claims VALUES('B', 1, '20171005', 30)

INSERT INTO #drug_claims VALUES('QUETIAPINE', 2, '20170828', 14)
INSERT INTO #drug_claims VALUES('QUETIAPINE', 2, '20171004', 14)
INSERT INTO #drug_claims VALUES('QUETIAPINE', 2, '20171029', 14)
INSERT INTO #drug_claims VALUES('NUPLPAZID', 2, '20170808', 14)
INSERT INTO #drug_claims VALUES('NUPLPAZID', 2, '20170822', 14)
INSERT INTO #drug_claims VALUES('NUPLPAZID', 2, '20170909', 14)
INSERT INTO #drug_claims VALUES('NUPLPAZID', 2, '20170924', 14)
INSERT INTO #drug_claims VALUES('NUPLPAZID', 2, '20171009', 14)
INSERT INTO #drug_claims VALUES('NUPLPAZID', 2, '20171026', 14)
INSERT INTO #drug_claims VALUES('NUPLPAZID', 2, '20171113', 14)
1 Like