延长结束日期，然后重复比较 [英] Extending end date then compare repeatedly
问题描述
我想做的是扩展RxEndDates，直到处方中不再有重叠.而且新的扩展名也不会重叠.
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.
上下文:如果Amy每天服用Humera并在其当前处方用完之前得到补充，则将第二个处方的DaySupply添加到第一个处方中.
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?
推荐答案
我认为该解决方案只能通过递归来实现，因为应该有一个循环来计算累积的DaySupply，我看不到任何非循环方法都可以做到这一点. -递归查找.您可以使用递归
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
也许您需要通过简化逻辑对其进行优化.但是请注意不要造成无限循环.调试时，请使用OPTION(MAXRECURSION N )和 N 而不是零.
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 .:如果我添加"Amy"，"Humera"，"2/15/2017"，"11"，"2/25/2017"，我也批评其他解决方案，那么这也可以. ..我很好奇它是否按预期工作-请进行测试！
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!
这篇关于延长结束日期，然后重复比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
