How to calculate overlapping days for 2 drugs

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
How do I calculate the difference between End date of Drug a and Startdate of drug b?

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.

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?

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

Select allOpen 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)

Select allOpen in new window

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

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

Select allOpen 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)

Select allOpen in new window

Thank you for your help!

Welcome. Glad to help.

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.

Question abandoned.
Provided verified solutions.