对同一表 SQL Server 中的连续日期范围进行分组

我有以下数据：

CREATE TABLE #Rate
(
    RateId Bigint
    ,PropertyId Bigint
    ,StartDate DATETIME
    ,EndDate DATETIME
)

INSERT INTO #Rate VALUES (100,1000,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (100,1000,'2015-01-12','2015-02-02')
INSERT INTO #Rate VALUES (100,1000,'2015-02-11','2015-02-25')
INSERT INTO #Rate VALUES (100,1002,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (100,1002,'2015-01-12','2015-02-02')
INSERT INTO #Rate VALUES (101,1000,'2015-02-11','2015-02-25')
INSERT INTO #Rate VALUES (101,1000,'2015-01-01','2010-01-11')
INSERT INTO #Rate VALUES (101,1000,'2015-01-12','2015-02-02')

我需要这个结果集

100 1000 '2015-01-01'  '2015-02-02'
100 1000 '2015-02-11'  '2015-02-25'
100 1002 '2015-01-01'  '2015-02-02'
101 1002 '2015-01-01'  '2015-02-02'

我需要分组RateId and propertyId和连续的日期范围。我已经使用游标完成了此操作，但我不需要游标，因为我们有很多记录。

如果我们可以从中创建视图，那就太好了:)

谢谢。

改变所有的2010 with 2015在您的数据中，您可以预期的实际结果集是

RateId               PropertyId           StartDate  EndDate
-------------------- -------------------- ---------- ----------
100                  1000                 2015-01-01 2015-02-02
100                  1000                 2015-02-11 2015-02-25
100                  1002                 2015-01-01 2015-02-02
101                  1000                 2015-01-01 2015-02-02
101                  1000                 2015-02-11 2015-02-25

这个问题与查找多行中连续日期的开始日期和结束日期 https://stackoverflow.com/questions/24289958/find-start-and-stop-date-for-contiguous-dates-in-multiple-rows所以我会用我的答案作为模板

WITH D AS (
  SELECT RateId, PropertyId, StartDate, EndDate
       , _Id = ROW_NUMBER() OVER (PARTITION BY  RateId, PropertyId 
                                  ORDER BY StartDate, EndDate)
  FROM   #Rate
), N AS (
  SELECT m.RateId, m.PropertyId, m.StartDate, m.EndDate
       , LastStop = p.EndDate 
  FROM   D m
         LEFT JOIN D p ON m.RateID = p.RateId 
                      AND m.PropertyId = p.PropertyId 
                      AND m._Id = p._Id + 1
), B AS (
  SELECT RateId, PropertyId, StartDate, EndDate, LastStop
       , Block = SUM(CASE WHEN LastStop Is Null Then 1
                          WHEN LastStop + 1 < StartDate Then 1
                          ELSE 0
                    END)
                 OVER (PARTITION BY RateId, PropertyId ORDER BY StartDate, EndDate)
  FROM   N
)
SELECT RateId, PropertyId
     , MIN(StartDate) StartDate
     , MAX(EndDate) EndDate
FROM   B
GROUP BY RateId, PropertyId, Block
ORDER BY RateId, PropertyId, Block;

D生成行计数器以避免使用三角连接。
N获取上一个EndDate在相同的RateID, PropertyID为每一行分组。
B为每个块生成一个序列号
主查询将数据聚合在B以获得想要的结果集。