Extending end date then compare repeatedly

overlapping dates example
date overlap logic in java
overlapping dates meaning
how to find overlapping time intervals in c#
overlapping date ranges meaning
check if two date ranges overlap excel
sql find overlapping time intervals same table
pandas overlapping date ranges

What I'd like to do is to extend RxEndDates until there is no more overlap in the prescriptions. And new extensions do not overlap either.

Context: If Amy takes Humera daily and gets a refill before her current prescription runs out, then add the DaySupply of the 2nd prescription to the first prescription.

sample data:
User Drug   RxStartDate DaySupply   RxEndDate
Amy Humera  2/12/2017   7   2/18/2017
Amy Humera  2/28/2017   5   3/4/2017 <--Overlap with below
Amy Humera  3/3/2017    5   3/7/2017 <--Overlap with above, need to combine
Amy Humera  3/8/2017    2   3/9/2017
Amy Humera  3/10/2017   7   3/16/2017
Amy Humera  3/17/2017   30  4/15/2017 <--Overlap with all below, combine
Amy Humera  3/22/2017   2   3/23/2017 <--Overlap
Amy Humera  3/24/2017   2   3/25/2017 <--Overlap
Amy Humera  3/31/2017   3   4/2/2017  <--Overlap
Amy Humera  4/7/2017    5   4/11/2017 <--Overlap
Amy Humera  4/13/2017   30  5/12/2017 <--Overlap

So after we combine, we get

User Drug   RxStartDate DaySupply   RxEndDate
Amy Humera  2/12/2017   7   2/18/2017
Amy Humera  2/28/2017   10  3/9/2017 <-- Combined from above, new overlap
Amy Humera  3/8/2017    2   3/9/2017 <-- Now this overlaps with above
Amy Humera  3/10/2017   7   3/16/2017
Amy Humera  3/17/2017   72  5/27/2017

User Drug   RxStartDate DaySupply   RxEndDate
Amy Humera  2/12/2017   7   2/18/2017
Amy Humera  2/28/2017   12  3/11/2017 <-- Combined, again, new overlap
Amy Humera  3/10/2017   7   3/16/2017 <-- Now this overlaps with above
Amy Humera  3/17/2017   72  5/27/2017

User Drug   RxStartDate DaySupply   RxEndDate
Amy Humera  2/12/2017   7   2/18/2017
Amy Humera  2/28/2017   19  3/18/2017 <-- Combined, again, new overlap
Amy Humera  3/17/2017   72  5/27/2017 <-- Now this overlaps with above

User Drug   RxStartDate DaySupply   RxEndDate
Amy Humera  2/12/2017   7   2/18/2017
Amy Humera  2/28/2017   91  5/29/2017
There is no more overlap…finished!     

Is there a way to do this automatically in a loop or something...any ideas?

I think the solution can only be implemented by recursion, as there should be a loop that calculates the accumulated DaySupply and I see no way of doing that with any non-recursive lookups. You can do this with recursive CTE - and according to the official doc, it is available starting with SQL Server 2008. A possible implementation (I added some test data to challenge it):

DECLARE @test TABLE (
    [User] VARCHAR(100),
    Drug VARCHAR(100),
    RxStartDate DATE,
    DaySupply INT,
    RxEndDate DATE
)

INSERT @test
VALUES
    ('Amy', 'Humera', '2/12/2017', '7', '2/18/2017'),
    ('Amy', 'Humera', '2/28/2017', '5', '3/4/2017'),
    ('Amy', 'Humera', '3/3/2017', '5', '3/7/2017'),
    ('Amy', 'Humera', '3/8/2017', '2', '3/9/2017'),
    ('Amy', 'Humera', '3/10/2017', '7', '3/16/2017'),
    ('Amy', 'Humera', '3/17/2017', '30', '4/15/2017'),
    ('Amy', 'Humera', '3/22/2017', '2', '3/23/2017'),
    ('Amy', 'Humera', '3/24/2017', '2', '3/25/2017'),
    ('Amy', 'Humera', '3/31/2017', '3', '4/2/2017'),
    ('Amy', 'Humera', '4/7/2017', '5', '4/11/2017'),
    ('Amy', 'Humera', '4/13/2017', '30', '5/12/2017'),

    ('Amy', 'Other', '3/24/2017', '7', '3/30/2017'),
    ('Amy', 'Other', '3/31/2017', '3', '4/2/2017'),
    ('Amy', 'Other', '4/7/2017', '5', '4/11/2017'),
    ('Amy', 'Other', '4/13/2017', '30', '5/12/2017'),

    ('Joe', 'Humera', '3/24/2017', '8', '3/31/2017'),
    ('Joe', 'Humera', '3/31/2017', '3', '4/2/2017'),
    ('Joe', 'Humera', '4/12/2017', '5', '4/16/2017'),
    ('Joe', 'Humera', '4/23/2017', '30', '5/22/2017'),

    ('Joe', 'Other', '3/24/2017', '60', '5/23/2017'),
    ('Joe', 'Other', '3/31/2017', '3', '4/2/2017'),
    ('Joe', 'Other', '4/7/2017', '5', '4/11/2017'),
    ('Joe', 'Other', '4/13/2017', '30', '5/12/2017')



-- You can comment this out, it is just to show progress:
SELECT * FROM @test ORDER BY [User], Drug, RxStartDate



DECLARE @test_2 TABLE (
    [User] VARCHAR(100),
    Drug VARCHAR(100),
    RxStartDate_base DATE,
    DaySupplyCumulative INT
)

;WITH CTE_RxEndDateExtended as (
    SELECT [User], Drug, RxStartDate, DaySupply, DaySupply as DaySupplyCumulative, RxStartDate as RxStartDate_base, RxStartDate as RxStartDateExtended, dateadd (dd, DaySupply, RxStartDate) as RxEndDateExtended
    FROM @test
    -- WHERE [User] = 'Amy' and Drug = 'Humera' and RxStartDate = '2/28/2017'
    UNION ALL
    SELECT t.[User], t.Drug, t.RxStartDate, t.DaySupply, c.DaySupplyCumulative + t.DaySupply as DaySupplyCumulative, c.RxStartDate_base, t.RxStartDate as RxStartDateExtended, dateadd (dd, t.DaySupply, c.RxEndDateExtended) as RxEndDateExtended
    FROM CTE_RxEndDateExtended as c INNER JOIN @test as t
        on c.[User] = t.[User] and c.Drug = t.Drug
            and c.RxEndDateExtended >= t.RxStartDate and c.RxStartDateExtended < t.RxStartDate
)
INSERT @test_2
SELECT [User], Drug, RxStartDate_base, MAX (DaySupplyCumulative) as DaySupplyCumulative -- comment this out and use this for debugging: SELECT *
FROM CTE_RxEndDateExtended
GROUP BY [User], Drug, RxStartDate_base -- comment this out for debugging
OPTION (MAXRECURSION 0) -- comment this out and use this for debugging (to avoid infinite loops): OPTION (MAXRECURSION 1000)



-- You can comment this out, it is just to show progress:
SELECT * FROM @test_2
ORDER BY [User], Drug, RxStartDate_base -- comment this out and use this for debugging: ORDER BY [User], Drug, RxStartDate_base, RxStartDate, DaySupplyCumulative



SELECT base.*, dateadd (dd, base.DaySupplyCumulative - 1, base.RxStartDate_base) as RxEndDateCumulative
FROM @test_2 as base LEFT OUTER JOIN @test_2 as filter
    on base.[User] = filter.[User] and base.Drug = filter.Drug
        and base.RxStartDate_base > filter.RxStartDate_base
        and dateadd (dd, base.DaySupplyCumulative, base.RxStartDate_base) <= dateadd (dd, filter.DaySupplyCumulative, filter.RxStartDate_base)
WHERE filter.[User] IS NULL
ORDER BY [User], Drug, RxStartDate_base

Maybe you need to optimize it by simplifying the logic. But be careful not to make an infinite loop. When debugging use OPTION (MAXRECURSION N) with N other than zero.

PS.: this one works also if I add 'Amy', 'Humera', '2/15/2017', '11', '2/25/2017', with which I was criticizing the other solutions... I am curious if it works as you expect - please test!

Determining if Two Date Ranges Overlap, It seemed like a lot of work to be able to compare date ranges with these four possibilities. Low and behold, there are only two: Date Range A ends before Date If one of these is true, then the two date ranges do not overlap. The stackoverflow.com answer referred me to a cool extension of this work. Extending end date then compare repeatedly. 1. 0. What I'd like to do is to extend RxEndDates until there is no more overlap in the prescriptions. And new extensions

You can identify where a group starts, using not exists. Then do a cumulative sum to assign a group . . . and aggregate. The following assumes a unique id, which is sort of needed to handle duplicates:

select [user], drug, grp, sum(daysupply), min(RxStartDate), max(RxEndDate)
    from (select t.*, sum(flg) over (partition by [user], drug order by RxStartDate) as grp
          from (select t.*,
                       (case when exists (select 1
                                          from @test t2
                                          where t2.[user] = t.[user] and t2.drug = t.drug and
                                                t2.RxStartDate < t.RxStartDate and
                                                t2.RxEndDate >= dateadd(day, -1, t.RxStartDate)
                                         )
                             then 0 else 1
                        end) as flg
                from @test t
               ) t
          ) t
    group by [user], drug, grp;

Multivariate Analysis of Variance and Repeated Measures: A , This process can then be repeated, examining contrast 15(ii). test comparing the overall effects of a factor, rather than the case described above of testing single contrasts. We shall now extend our single contrast case to include this situation. simply adding together the sums of squares due to each component contrast. Compare dates if greater than another date with formula As below screenshot shown, you can compare dates in the Date column with a specified date 2015/3/10 and finally get the certain dates which are greater than it in that column with formula as follows.

I used a CTE Common Table Expression to perform the grouping. Since some of the days don't technically overlap, I created an alternate end date [RxEndDate_ALT] by adding 1 to the [RxEndDate] in the source_data. Then I was able to group the dates using NOT EXISTS in source_data_grouped. After that, I join back to the source_data_raw to SUM the [DaySupply].


Results


SQL
WITH 
source_data_raw
AS 
(
    SELECT tbl.* FROM (VALUES
      ( 'Amy', 'Humera', 7, CAST('12-Feb-2017' AS DATE), CAST('18-Feb-2017' AS DATE))
    , ( 'Amy', 'Humera', 5, '28-Feb-2017', '04-Mar-2017')
    , ( 'Amy', 'Humera', 5, '03-Mar-2017', '07-Mar-2017')
    , ( 'Amy', 'Humera', 2, '08-Mar-2017', '09-Mar-2017')
    , ( 'Amy', 'Humera', 7, '10-Mar-2017', '16-Mar-2017')
    , ( 'Amy', 'Humera', 30, '17-Mar-2017', '15-Apr-2017')
    , ( 'Amy', 'Humera', 2, '22-Mar-2017', '23-Mar-2017')
    , ( 'Amy', 'Humera', 2, '24-Mar-2017', '25-Mar-2017')
    , ( 'Amy', 'Humera', 3, '31-Mar-2017', '15-Apr-2017')
    , ( 'Amy', 'Humera', 5, '07-Apr-2017', '16-Apr-2017')
    , ( 'Amy', 'Humera', 30, '13-Apr-2017', '27-May-2017')
    ) tbl ([User], [Drug], [DaySupply], [RxStartDate], [RxEndDate]) 
) 
, 
source_data
AS
(
    SELECT 
          sdr.[User]
        , sdr.[Drug]
        , sdr.[RxStartDate]
        , sdr.[RxEndDate]
        , [RxEndDate_ALT] = DATEADD(DAY, 1, sdr.[RxEndDate])
    FROM 
        source_data_raw AS sdr
)
, 
source_data_grouped
AS
(
    SELECT 
          s1.[User]
        , s1.[Drug]
        , s1.[RxStartDate]
        , [RxEndDate] = MIN(t1.[RxEndDate]) 
    FROM 
        source_data AS s1 
        INNER JOIN source_data AS t1 ON s1.[User] = t1.[User] AND s1.[Drug] = t1.[Drug] AND s1.[RxStartDate] <= t1.[RxEndDate_ALT]
            AND NOT EXISTS 
                (
                    SELECT 1
                    FROM source_data AS t2
                    WHERE 
                        1=1
                        AND t1.[User] = t2.[User]
                        AND t1.[Drug] = t2.[Drug]
                        AND t1.[RxEndDate_ALT] >= t2.[RxStartDate]
                        AND t1.[RxEndDate_ALT] < t2.[RxEndDate_ALT]
                ) 
    WHERE 
        1=1
        AND NOT EXISTS 
        (
            SELECT 1
            FROM source_data AS s2
            WHERE 
                1=1
                AND s1.[User] = s2.[User]
                AND s1.[Drug] = s2.[Drug]
                AND s1.[RxStartDate] > s2.[RxStartDate]
                AND s1.[RxStartDate] <= s2.[RxEndDate_ALT]
        )
    GROUP BY 
          s1.[User]
        , s1.[Drug]
        , s1.[RxStartDate]
)
SELECT 
      sdg.[User]
    , sdg.[Drug]
    , [DaySupply] = SUM(sdr.[DaySupply])
    , sdg.[RxStartDate]
    , sdg.[RxEndDate]
FROM 
    source_data_grouped AS sdg
    INNER JOIN source_data_raw AS sdr ON sdr.[RxStartDate] BETWEEN sdg.[RxStartDate] AND sdg.[RxEndDate]
GROUP BY 
      sdg.[User]
    , sdg.[Drug]
    , sdg.[RxStartDate]
    , sdg.[RxEndDate]

Management of Common Musculoskeletal Disorders: Physical Therapy , For extenSion, have the patient straighten the knee, then tell him or her to hold it extended Compare the amount of passive extension to extension maintained against gravity. To complete the last 15° of knee extension, a 60% increase in force of the quadriceps muscles is required. The movements can be repeated. ii. you are checking like start date <= end date this condition is true in your example like start date 12/02/2014 and end date 13/03/2014, so its showing wrong message. if you need to check that, start date is not greater than end date, then you need to check like this..

Fill dates between dates with Power BI / Power Query, If you need to fill in consecutive dates between two dates then this is the best way to make that happen. We had a start and an end date, but what if you only have the start After expanding the new column this is how it looks like: the alarm should go off the first time, then for how many times it has to go  FROM ( SELECT TOP 100 n, m ,StartDate -- Align the end dates with the start date on the next row ,EndDate = LEAD(StartDate, 1) OVER (PARTITION BY n ORDER BY StartDate) FROM ( SELECT a.n, m=b.n

Fixed-term workers, full redundancy rights (if continuously employed for two years or more) As a fixed-term employee, you can compare your treatment to the treatment of a fixed-term contract, no notice of the contract reaching its end date will need to be given statutory minimum, then you have to give your employer this length of service. Determine if dates has passed with Conditional Formatting. With the Conditional Formatting function, you can highlight all dates which are overdue compared to today’s date. 1. Select the date list, and click Conditional Formatting > New Rule under Home tab. See screenshot: 2. In the New Formatting Rule dialog box, you need to: A.

2. Project Management Overview – Project Management, They are not an everyday business process and have definitive start dates and without an ending date and with the same processes repeated to produce the same results. If the answer is yes to all these questions, then we have a project. you will have to extend your schedule because the resources you have take  You can use DateTime.Compare() If Result is less than that means first date is less than second and 0 means equal and greater. DateTime dileverydate = Convert.ToDateTime(dileveryDate.Text); var todaysDate = DateTime.Today; int result = DateTime.Compare(dileverydate, todaysDate);

Comments
  • What version of SQL Server are you using?
  • @rosie chi: Don't you think this question should be merged with your other one? I keep testing these solutions but seems to me that only recursion could do the job. And in that post I already showed one implementation so others should see that also before coding the same thing.
  • @DávidLaczkó: I think that is a great idea! I have read the 'merged' link you indicated, but it looks like that I am unable to do the merge, only moderators. Is it possible for you to potentially post your answer from that post over here?
  • @aduguid: I guess this is important...I'm using SQL Server 2008. Thanks!
  • OK, I copied my answer here. Also marked the other question to be closed (nothing added there since).
  • WaHoo! This solution worked for me with SQL Server 2008. I made a tiny change...add a '=' after the last '<' when comparing the 2 dateadds, as it was showing weird results without it. I am unable to edit the code itself as the change is too minute (only changes of 6 characters or above can be done). Anyways, thank you very much for your assistance all the way from the other post to this one!
  • Do you mean this line should have <=: and dateadd (dd, base.DaySupplyCumulative, base.RxStartDate_base) < dateadd (dd, filter.DaySupplyCumulative, filter.RxStartDate_base) ?
  • That is a nice correction, I updated the post. Thanks for the feedback!
  • Thanks for your response...I'm getting an error code of: Incorrect syntax near 'order' and Incorrect syntax near 't' when I run the code... I feel like this is so close, would you mind helping me check what is going on? Your code is hard to wrap my head around :-)
  • Ok, so I've removed the ORDER BY in the window function since I'm not sure that it is necessary (maybe it is?) and now the error is :Incorrect syntax near 'grp' after the GROUP BY
  • Also, I have structured my data in a way where the prescriptions are already ordered by the drug, then the RxStartDate, and I have already removed any duplicates, so that the same drug should never have the same RxStartDate
  • @rosiechi . . . The order by is quite critical to the window function. Otherwise, grp is not set correctly.
  • Thank you! I just read up on the order by part, and figured out that my server does not support this function, which is why I was getting the error!
  • This doesn't seem to return the correct daysupply. And this one has the same issue as Gordon Linoff's: this solution happens to work with the sample data, but if I add 'Amy', 'Humera', '2/15/2017', '11', '2/25/2017', I don't get the correct result of having only one consecutive interval. It says I have 2 intervals, one 18 days and one 91 days. If you add 18-1 days to 2/12/2017 it becomes 3/1/2017 and that is a new overlap with 2/28/2017. I think a non-recursive approach won't work in the general case as something needs to keep track of the accumulated overlapping days.