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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ste5anSenior 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
Dale FyeOwner, 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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

PortletPaulEE Topic AdvisorCommented:
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
Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Shel HankleyBusiness Analyst IIIAuthor Commented:
Thank you for your help!
0
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help.
0
PortletPaulEE Topic AdvisorCommented:
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
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.