# How to calculate overlapping days for 2 drugs

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.

Does anyone have any ideas on how to do this?  Thank you!
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior DeveloperCommented:
According to your description, it is not overlapping, but total days of therapy. This means MIN(start) to MAX(DATEADD(DAY, duration, start)).. to be precise it is more like FIFO..

``````DECLARE @Sample TABLE
(
DrugID NCHAR(1) ,
StartDate DATE ,
SupplyDurationDaysNumber INT
);

INSERT INTO @Sample ( DrugID ,
StartDate ,
SupplyDurationDaysNumber )
VALUES ( N'A', '20170108', 30 ) ,
( N'A', '20170109', 30 ) ,
( N'A', '20170110', 30 ) ,
( N'B', '20170509', 30 ) ,
( N'B', '20170510', 30 );

SELECT   S.DrugID ,
MIN(S.StartDate) ,
SUM(S.SupplyDurationDaysNumber) ,
FROM     @Sample S
GROUP BY S.DrugID;
``````

No overlap at all:
0
I think the dates were switched around.  I used your code to adjust
DECLARE @Sample TABLE
(
DrugID NCHAR(1) ,
StartDate DATE ,
SupplyDurationDaysNumber INT
);

INSERT INTO @Sample ( DrugID ,
StartDate ,
SupplyDurationDaysNumber )
VALUES ( N'A', '20170801', 30 ) ,
( N'A', '20170901', 30 ) ,
( N'A', '20171001', 30 ) ,
( N'B', '20170905', 30 ) ,
( N'B', '20171005', 30 );

SELECT   S.DrugID ,
MIN(S.StartDate) as [startdate] ,
SUM(S.SupplyDurationDaysNumber)as [duration] ,
FROM     @Sample S
GROUP BY S.DrugID;

which gave me
How do I calculate the difference between End date of Drug a and Startdate of drug b?
0
Owner, Developing Solutions LLCCommented:
So are you actually trying to compute the # of days on meds, or the overlap.  I'm not certain ste5an's min/max method would work, depending on exactly how you answer that question.  Additionally, give that August and October have 31 days, your start and duration periods for med A would have a 1 day gap at the end of each of those months.

Another method would be to create a query that creates a day for each day in on meds, and then count the distinct days or the days where there are two records.  To do this, I would create a tally table (tblNumbers with one field lngNumber) with numbers from 0 to 99 (would cover a 90 day supply)

Then, assuming that your medication type and dates listed above would show up in a table ClientMeds, I would do something like:

SELECT ClientMeds.ClientID, ClientMeds.Medication, DateAdd("d", ClientMeds.Duration, ClientMeds.StartDate) as OnMeds
FROM ClientMeds, tblNumbers
WHERE ClientID = X
AND ClientMeds.Medication IN ("A", "B")
AND tblNumbers.lngNumber <= ClientMeds.Duration

With this query, you could then get the number of days, or the number of days overlap.
0
How does the data tell us to consider drug a and drug b need to be considered together.  You mention same class but it isn't shown as available data.

You also seem to imply that this is a particular course of medication. Is there a patient id involved?

0
Database ExpertCommented:

Added , ISNULL(DATEDIFF(d,[startdate],LAG(EndDate) OVER (ORDER BY DrugID)),0) NumberofDays

Check out the last column -> NumberofDays difference.

``````DECLARE @Sample TABLE
(
DrugID NCHAR(1) ,
StartDate DATE ,
SupplyDurationDaysNumber INT
);

INSERT INTO @Sample ( DrugID ,
StartDate ,
SupplyDurationDaysNumber )
VALUES ( N'A', '20170801', 30 ) ,
( N'A', '20170901', 30 ) ,
( N'A', '20171001', 30 ) ,
( N'B', '20170905', 30 ) ,
( N'B', '20171005', 30 );

SELECT * , ISNULL(DATEDIFF(d,[startdate],LAG(EndDate) OVER (ORDER BY DrugID)),0) NumberofDays
FROM
(
SELECT   S.DrugID ,
MIN(S.StartDate) as [startdate] ,
SUM(S.SupplyDurationDaysNumber)as [duration] ,
FROM     @Sample S
GROUP BY S.DrugID
)k
``````

OUTPUT
``````/*------------------------
OUTPUT
------------------------*/
DrugID startdate  duration    EndDate    NumberofDays
------ ---------- ----------- ---------- ------------
A      2017-08-01 90          2017-10-30 0
B      2017-09-05 60          2017-11-04 55

(2 row(s) affected)
``````
0
I think Im almost there, that is great!  I just need to add the duration of drugs A and B 90 + 60  and then subtract the number of days 55 to get 95 but I am getting
DrugID      startdate      duration      EndDate      NumberofDays      diff
A      2017-08-01      90      2017-10-30      0      90
B      2017-09-05      60      2017-11-04      55      5
0
Database ExpertCommented:

``````DECLARE @Sample TABLE
(
DrugID NCHAR(1) ,
StartDate DATE ,
SupplyDurationDaysNumber INT
);

INSERT INTO @Sample ( DrugID ,
StartDate ,
SupplyDurationDaysNumber )
VALUES ( N'A', '20170801', 30 ) ,
( N'A', '20170901', 30 ) ,
( N'A', '20171001', 30 ) ,
( N'B', '20170905', 30 ) ,
( N'B', '20171005', 30 );

SELECT *
, ISNULL(DATEDIFF(d,[startdate],LAG(EndDate) OVER (ORDER BY DrugID)),0) NumberofDays
, [duration] - ISNULL(DATEDIFF(d,[startdate],LAG(EndDate) OVER (ORDER BY DrugID)),0) diff
FROM
(
SELECT   S.DrugID ,
MIN(S.StartDate) as [startdate] ,
SUM(S.SupplyDurationDaysNumber)as [duration] ,
FROM     @Sample S
GROUP BY S.DrugID
)k
``````

OUTPUT

``````/*------------------------
OUTPUT
------------------------*/
DrugID startdate  duration    EndDate    NumberofDays diff
------ ---------- ----------- ---------- ------------ -----------
A      2017-08-01 90          2017-10-30 0            90
B      2017-09-05 60          2017-11-04 55           5

(2 row(s) affected)
``````
0
Database ExpertCommented:
>>I think Im almost there, that is great!  I just need to add the duration of drugs A and B 90 + 60  and then subtract the number of days 55 to get 95
Updated. Please try ..It is matching exactly what you need.

``````DECLARE @Sample TABLE
(
DrugID NCHAR(1) ,
StartDate DATE ,
SupplyDurationDaysNumber INT
);

INSERT INTO @Sample ( DrugID ,
StartDate ,
SupplyDurationDaysNumber )
VALUES ( N'A', '20170801', 30 ) ,
( N'A', '20170901', 30 ) ,
( N'A', '20171001', 30 ) ,
( N'B', '20170905', 30 ) ,
( N'B', '20171005', 30 );

SELECT *
, ISNULL(DATEDIFF(d,[startdate],LAG(EndDate) OVER (ORDER BY DrugID)),0) NumberofDays
,SUM([duration]) OVER (ORDER BY DrugID) - ISNULL(DATEDIFF(d,[startdate],LAG(EndDate) OVER (ORDER BY DrugID)),0) diff
FROM
(
SELECT   S.DrugID ,
MIN(S.StartDate) as [startdate] ,
SUM(S.SupplyDurationDaysNumber)as [duration] ,
FROM     @Sample S
GROUP BY S.DrugID
)k
``````

OUTPUT
``````/*------------------------
OUTPUT
------------------------*/

DrugID startdate  duration    EndDate    NumberofDays diff
------ ---------- ----------- ---------- ------------ -----------
A      2017-08-01 90          2017-10-30 0            90
B      2017-09-05 60          2017-11-04 55           95

(2 row(s) affected)
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

0
Database ExpertCommented:
0
How will you apply this to the actual data? Your words and your sample data do not match sufficiently

>>"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:"

WHERE is "class" in the sample data? How will the work done so far apply when you have many more rows of data?

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

WHO is receiving therapy ? How are "they" identified in the sample data?

The calculations you want are possible, but you I believe you need them "per person, per class". Without this vital information I'm afraid you only have a partial solution. (nb: No criticism intended to participants!)

So, I would like to repeat my earlier request for you to expand on the sample data to be closer to your actual needs.
1
Database ExpertCommented:
Question abandoned.
Provided verified solutions.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.