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

Changing all the 2010 with 2015 in your data the actual resultset you can expect is

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

这个问题非常类似于find多行中连续日期的开始和停止日期，所以我将使用我对该答案的回答作为模板

this question is quite similar to find start and stop date for contiguous dates in multiple rows so I'll use my answer to that one as a template

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 获取同一 RateID, PropertyID 组中每一行的前一个 EndDate.
B 为每个区块生成一个序列号
主查询聚合B中的数据，得到想要的结果集.

D generates a row counter to avoid to use triangular join.
N get the previous EndDate in the same RateID, PropertyID group for every row.
B generate a sequence number for every block
The main query aggregates the data in B to get the wanted resultset.