我需要有关编写以下问题的最佳查询的帮助。附加了我的查询，但查询资源利用率很高。
下面是实现上述逻辑的代码。请提出实现相同目标的一些最佳方法
-- drop table #me
create table #ME (memid int , EffectiveDate datetime , termdate datetime)
Insert into #ME values ('123','3-Dec-16','10-Jan-17')
Insert into #ME values ('123','11-Jan-17','6-Feb-17')
Insert into #ME values ('123','7-Feb-17','5-Mar-17')
Insert into #ME values ('123','8-Mar-17','15-Apr-17')
Insert into #ME values ('123','16-Apr-17','24-May-17')
--drop table #dim
select * from #ME
declare @StartDate datetime , @CutoffDate datetime
select @StartDate= min(effectivedate),@CutoffDate = max(termdate) From #me where termdate<>'9999-12-31 00:00:00.000'
SELECT d
into #dim
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
-- on my system this would support > 5 million days
ORDER BY s1.[object_id]
) AS x
) AS y;
--drop table #MemEligibilityDateSpread
select MemID, D As DateSpread Into #MemEligibilityDateSpread From #Dim dim JOIN #me ME on dim.d between ME.effectivedate and me.termdate
--drop table #DateClasified
WITH CTE AS
(
SELECT MEmID,
UniqueDate = DateSpread,
DateGroup = DATEADD(dd, - ROW_NUMBER() OVER (PARTITION BY Memid ORDER BY Memid,DateSpread), DateSpread)
FROM #MemEligibilityDateSpread
GROUP BY Memid,DateSpread
)
--===== Now, if we find the MIN and MAX date for each DateGroup, we'll have the
-- Start and End dates of each group of contiguous daes. While we're at it,
-- we can also figure out how many days are in each range of days.
SELECT Memid,
StartDate = MIN(UniqueDate),
EndDate = MAX(UniqueDate)
INTO #DateClasified
FROM cte
GROUP BY Memid,DateGroup
ORDER BY Memid,StartDate
select ME.MemID,ME.EffectiveDate,ME.TermDate,DC.StartDate,DC.EndDate from #DateClasified dc join #me ME ON Me.MemID = dc.MemID
and (ME.EffectiveDate BETWEEN DC.StartDate AND DC.EndDate
OR ME.TermDate BETWEEN DC.StartDate AND DC.EndDate)
最佳答案
在cte0和cte1中，我们创建了一个临时的提示/日历表。一旦有了这些，就可以对Island进行计算和分组，这是一件小事。
当前，理算最大最长为10,000天（27年），但是您可以通过添加, cte0 N5轻松扩展理算表
;with cte0(N) as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
,cte1(R,D) as (Select Row_Number() over (Order By (Select Null))
,DateAdd(DD,-1+Row_Number() over (Order By (Select Null)),(Select MinDate=min(EffectiveDate) From #ME))
From cte0 N1, cte0 N2, cte0 N3, cte0 N4)
Select MemID
,EffectiveDate
,TermDate
,SinceFrom = Min(EffectiveDate) over (Partition By Island)
,Tildate = Max(TermDate) over (Partition By Island)
From (
Select *,Island = R - Row_Number() over (Partition By MemID Order by TermDate)
From #ME A
Join cte1 B on D Between EffectiveDate and TermDate
) A
Group By MemID,Island,EffectiveDate,TermDate
Order By 1,2
退货
MemID EffectiveDate TermDate SinceFrom Tildate
123 2016-12-03 2017-01-10 2016-12-03 2017-03-05
123 2017-01-11 2017-02-06 2016-12-03 2017-03-05
123 2017-02-07 2017-03-05 2016-12-03 2017-03-05
123 2017-03-08 2017-04-15 2017-03-08 2017-05-24
123 2017-04-16 2017-05-24 2017-03-08 2017-05-24
编辑-现在，如果您想要压缩的数据集
Select MemID
,EffectiveDate = Min(EffectiveDate)
,TermDate = Max(TermDate)
From (
Select *,Island = R - Row_Number() over (Partition By MemID Order by TermDate)
From #ME A
Join cte1 B on D Between EffectiveDate and TermDate
) A
Group By MemID,Island
Order By 1,2
退货
MemID EffectiveDate TermDate
123 2016-12-03 2017-03-05
123 2017-03-08 2017-05-24