循环更新 - 更好的方法? [英] Looping updates - better way to do this?
问题描述
我被这个问题困住了.我在想我可能需要一个循环更新(如果存在的话)，但也许有更好的方法?
I am stuck on this problem. I am thinking that I may need a looping update (if that exists), but maybe there is a better way?
我正在处理索赔药物处方数据，所以基本上是 5 列用户、药物、RxStartDate、DaySupply 和'RxEndDate' = dateadd(dd, DaySupply-1, RxStartDate)
I am working with claims drug prescription data, so essentially 5 columns User, Drug, RxStartDate, DaySupply, and 'RxEndDate' = dateadd(dd, DaySupply-1, RxStartDate)
如果同一个用户有 2 个重叠的处方(Rx1 EndDate >= Rx2 StartDate)，那么我需要将 DaySupply 相加.
If the same user has 2 prescriptions that overlap (Rx1 EndDate >= Rx2 StartDate), then I need to sum the DaySupply together.
一旦我对 DaySupply 求和，RxEndDate 就会延长，我需要再次检查处方中是否存在重叠.
Once I sum the DaySupply, the RxEndDate will extend and I need to check again if there is overlap in the prescription.
目前我有以下代码，我必须运行并重新运行，直到我不再有更新，但我知道必须有更好的方法来做到这一点......
Currently I have the following code that I have to run and re-run until I don't have anymore updates, but I know there must be a better way to do this...
UPDATE b
SET b.RxStartDate= a.RxStartDate
FROM RxClaims a
JOIN RxClaims b on a.User=b.User and a.Drug = b.Drug
WHERE b.RxStartDate<= a.RxEndDate
and a.RxStartDate< b.RxStartDate
SELECT User, Drug, RxStartDate, sum(DaySupply) as DaySupply,
'RxEndDate' = dateadd(dd, sum(DaySupply)-1, RxStartDate)
into RxClaims2
from RxClaims
group by User, Drug, RxStartDate
有人想吗?
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
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
after 1st time running my current code
User Drug RxStartDate DaySupply RxEndDate
Amy Humera 2/12/2017 7 2/18/2017
Amy Humera 2/28/2017 10 3/9/2017
Amy Humera 3/8/2017 2 3/9/2017
Amy Humera 3/10/2017 7 3/16/2017
Amy Humera 3/17/2017 72 5/27/2017
after 2nd time running my current code
User Drug RxStartDate DaySupply RxEndDate
Amy Humera 2/12/2017 7 2/18/2017
Amy Humera 2/28/2017 12 3/11/2017
Amy Humera 3/10/2017 7 3/16/2017
Amy Humera 3/17/2017 72 5/27/2017
after 3rd time running my current code
User Drug RxStartDate DaySupply RxEndDate
Amy Humera 2/12/2017 7 2/18/2017
Amy Humera 2/28/2017 19 3/18/2017
Amy Humera 3/17/2017 72 5/27/2017
after 4th time running my current code
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!
推荐答案
我认为解决方案只能通过递归来实现，因为应该有一个循环来计算累积的 DaySupply，我认为没有任何非-递归查找.您可以使用 递归 CTE.
一种可能的实现方式:
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.
A possible implementation:
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.
这篇关于循环更新 - 更好的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
