消除和减少重叠日期范围

Question

我有一组日期范围，包含部分重叠和完全重叠的日期，就像这样:

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，我想要为每个用户创建一个新的数据集，消除重叠数据，扩展范围并在必要时删除冗余数据，最终得到类似于以下内容：

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)

如果需要，游标是可以的，但如果我可以不使用它们那就更好了。

- Jakob Möllås

---

SQL Server的哪个版本，2005+？ - RichardTheKiwi

Answer 1

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替换为您的表名

- RichardTheKiwi

---

很棒的东西！能够完美运作！我得研究一下**DENSE_RANK()**的用法，这对我来说是新的。谢谢！ - Jakob Möllås

值得一提的是，对于大于 master..spt_values 返回行数的日期跨度，这种方法将不起作用。在这种情况下，您可以将该表与自身进行交叉连接以获得更大的窗口大小。 - Chris Pickford

一些有关DENSE_RANK()的文档：https://learn.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql - Westy92