消除和减少重叠的日期范围 [英] Eliminate and reduce overlapping date ranges

问题描述

我有一组日期范围，包括部分重叠和完全重叠的日期，如下所示:

I have a set of date ranges consisting of both partially and fully overlapping dates, like this:

UserID  StartDate   EndDate 
======  ==========  ==========
1       2011-01-01  2011-01-02  <- A
1       2011-01-01  2011-01-10  <- A
1       2011-01-08  2011-02-15  <- A
1       2011-02-20  2011-03-10  <- B
2       2011-01-01  2011-01-20  <- C
2       2011-01-15  2011-01-25  <- C

使用 T-SQL，我想为每个用户创建一组新数据，消除重叠数据，扩展范围并在需要时删除冗余数据，结果如下:

Using T-SQL, I would like to create a new set of data, per user, with eliminated overlapping data, extending ranges and removing redundant data where needed, resulting in something like this:

UserID  StartDate   EndDate 
======  ==========  ==========
1       2011-01-01  2011-02-15 ('A', three rows combined, extending the range)
1       2011-02-20  2011-03-10 ('B', no change, no overlaps here)
2       2011-01-01  2011-01-25 ('C', two rows combined)

如果需要，光标很好，但如果我可以不用它们，那就更好了.

Cursors are fine if needed, but if I can do without them that would be even better.

推荐答案

对于 SQL Server 2005+

For SQL Server 2005+

-- sample table with data
declare @t table(UserID int, StartDate datetime, EndDate datetime)
insert @t select
1, '20110101', '20110102' union all select
1, '20110101', '20110110' union all select
1, '20110108', '20110215' union all select
1, '20110220', '20110310' union all select
2, '20110101', '20110120' union all select
2, '20110115', '20110125'

-- your query starts below

select UserID, Min(NewStartDate) StartDate, MAX(enddate) EndDate
from
(
    select *,
        NewStartDate = t.startdate+v.number,
        NewStartDateGroup =
            dateadd(d,
                    1- DENSE_RANK() over (partition by UserID order by t.startdate+v.number),
                    t.startdate+v.number)
    from @t t
    inner join master..spt_values v
      on v.type='P' and v.number <= DATEDIFF(d, startdate, EndDate)
) X
group by UserID, NewStartDateGroup
order by UserID, StartDate

注意事项:

1. 用你的表名替换@t

这篇关于消除和减少重叠的日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！