# 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?

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

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

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

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

0

Database ExpertCommented:
0

freelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.