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!
Shel HankleyBusiness Analyst IIIAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor 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] ,
         DATEADD(DAY, SUM(S.SupplyDurationDaysNumber), MIN(S.StartDate)) AS EndDate		 
FROM     @Sample S
GROUP BY S.DrugID
)k

Open in new window


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)

Open in new window

0
 
ste5anConnect With a Mentor 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) ,
         DATEADD(DAY, SUM(S.SupplyDurationDaysNumber), MIN(S.StartDate)) AS EndDate
FROM     @Sample S
GROUP BY S.DrugID;

Open in new window


No overlap at all:
Capture.PNG
0
 
Shel HankleyBusiness Analyst IIIAuthor Commented:
Thank you for your quick reply @ste5an
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] ,
         DATEADD(DAY, SUM(S.SupplyDurationDaysNumber), MIN(S.StartDate)) AS EndDate
FROM     @Sample S
GROUP BY S.DrugID;

which gave me sample.jpg
How do I calculate the difference between End date of Drug a and Startdate of drug b?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Dale FyeCommented:
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
 
PortletPaulfreelancerCommented:
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?

Could you provide all columns of your table or tables please?
0
 
Pawan KumarDatabase ExpertCommented:
Please try this-

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] ,
         DATEADD(DAY, SUM(S.SupplyDurationDaysNumber), MIN(S.StartDate)) AS EndDate		 
FROM     @Sample S
GROUP BY S.DrugID
)k

Open in new window


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)

Open in new window

0
 
Shel HankleyBusiness Analyst IIIAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Ok. Please try this -

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] ,
         DATEADD(DAY, SUM(S.SupplyDurationDaysNumber), MIN(S.StartDate)) AS EndDate		 
FROM     @Sample S
GROUP BY S.DrugID
)k

Open in new window


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)

Open in new window

0
 
Shel HankleyBusiness Analyst IIIAuthor Commented:
Thank you for your help!
0
 
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help.
0
 
PortletPaulfreelancerCommented:
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
 
Pawan KumarDatabase 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.

All Courses

From novice to tech pro — start learning today.