需要在SQL Server 2008中使用CTC来实现业务需求

对小规模的实施会有很大的帮助SP以满足以下要求。

业务规则是：

EndDate 应该是下一个规则的开始日期，否则规则不应该是 视为连续的，并将被视为其他规则系列，如 在下面的示例中显示有两个系列，因为第四个系列 它已经打破了记录。

RuleId  Name    StartDate           EndDate
2       TP1     1/1/2015 00.00.00   1/31/2015 00.00.00
2       TP1     1/31/2015 00.00.00  2/28/2015 00.00.00
2       TP1     2/28/2015 00.00.00  3/15/2015 00.00.00
2       TP1     3/18/2015 00.00.00  3/28/2015 00.00.00
2       TP1     3/28/2015 00.00.00  4/30/2015 00.00.00
2       TP1     4/30/2015 00.00.00  5/5/2015 00.00.00

结果应该是：

RuleId  Name    StartDate           EndDate
2       TP1     1/1/2015 00.00.00   3/15/2015 00.00.00
2       TP1     3/18/2015 00.00.00  5/5/2015 00.00.00

For Sql Server 2012+:

with cte1 as (select *,
case when lag(enddate) over(partition by ruleid order by startdate) = startdate 
     then 0 else 1 end as b
from t),
cte2 as(select *, sum(b) over(partition by ruleid order by startdate) as s
from cte1)
select ruleid, name, min(startdate), max(enddate) from cte2
group by s, ruleid, name

Fiddle http://sqlfiddle.com/#!6/aadf4/1 http://sqlfiddle.com/#!6/aadf4/1

对于 Sql Server 2008 使用：

WITH    cte1
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY startdate ) rn ,
                        CASE WHEN ( SELECT  MAX(enddate)
                                    FROM    @t ti
                                    WHERE   ti.ruleid = t.ruleid
                                            AND ti.startdate < t.startdate
                                  ) = startdate THEN 0
                             ELSE 1
                        END AS b
               FROM     @t t
             ),
        cte2
          AS ( SELECT   * ,
                        ( SELECT    SUM(b)
                          FROM      cte1 c11
                          WHERE     c11.ruleid = c1.ruleid
                                    AND c11.rn <= c1.rn
                        ) bb
               FROM     cte1 c1
             )
    SELECT  bb ,
            ruleid ,
            name ,
            MIN(startdate) ,
            CASE WHEN COUNT(*) = COUNT(enddate) THEN MAX(enddate)
                 ELSE NULL
            END
    FROM    cte2
    GROUP BY bb ,
            ruleid ,
            name