旧的答案有一个缺点：每行仅与前一行进行检查，以验证时间段是否重叠；如果前一行的时间段持续的时间更长，则逻辑将不考虑它。例如：
Code | DateStart| DateFrom | Overlap-----+---------------------+---------------------+---------1006 | 2014-06-18 07:00:00 | 2014-06-18 19:00:00 | 0 1006 | 2014-06-18 08:10:00 | 2014-06-18 10:00:00 | 1 1006 | 2014-06-18 16:00:00 | 2014-06-18 20:30:00 | 0 <- don't overlap with previous but overlap with the first
为了改善这种
PrevStop需要变得
LastStop和有以前的最大的价值
DateFrom为
Code
With N AS ( SELECt Code, DateFrom, DateTo , LastStop = MAX(DateTo) OVER (PARTITION BY Code ORDER BY DateFrom, DateTo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) FROM Table1), B AS ( SELECt Code, DateFrom, DateTo , Block = SUM(CASE WHEN LastStop Is Null Then 1 WHEN LastStop < DateFrom Then 1 ELSE 0 END) OVER (PARTITION BY Code ORDER BY DateFrom, LastStop) FROM N)SELECt Code , MIN(DateFrom) DateFrom , MAX(DateTo) DateToFROM BGROUP BY Code, BlockORDER BY Code, Block
[SQLFiddle Demo](http://www.sqlfiddle.com/#!6/a5c59/22)
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING需要从中删除当前行
MAX。
旧答案
此查询仅在不完全在先例中的每个期间才有效。
这个想法是检查每一行是否与下一个/上一个链接。
如果行被链接，则它们形成一个块，它们将被分组在一起以得到第一个
DateFrom和最后一个
DateTo
With N AS ( SELECt Code, DateFrom, DateTo , PrevStop = LAG(DateTo, 1, NULL) OVER (PARTITION BY Code ORDER BY DateFrom) FROM Table1), B AS ( SELECt Code, DateFrom, DateTo , Block = SUM(CASE WHEN PrevStop Is Null Then 1 WHEN PrevStop < DateFrom Then 1 ELSE 0 END) OVER (PARTITION BY Code ORDER BY PrevStop) FROM N)SELECt Code , MIN(DateFrom) DateFrom , MAX(DateTo) DateToFROM BGROUP BY Code, BlockORDER BY Code, Block
[SQLFiddle demo](http://www.sqlfiddle.com/#!6/e57dc/9)在同一代码/天上添加了一些数据以检查更多块
该查询搜索块启动器，检查每行是代码的第一行（
PrevStop IS NULL）还是在前一行（
PrevStop < DateFrom）之外。
窗口
SUM仅检索的前一行，
ORDER以为链接的数据块创建成本值，例如，使用测试数据，我们将获得
Code | DateStart| DateFrom | Starter | Block-----+---------------------+---------------------+---------+------1006 | 2014-06-18 07:00:00 | 2014-06-18 09:00:00 | 1 | 11006 | 2014-06-18 08:10:00 | 2014-06-18 06:00:00 | 0 | 11006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 | 0 | 11006 | 2014-06-18 07:00:00 | 2014-06-18 07:30:00 | 1 | 21006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 | 1 | 31006 | 2014-06-18 08:10:00 | 2014-06-18 09:00:00 | 0 | 33006 | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 | 1 | 13006 | 2014-06-18 09:00:00 | 2014-06-18 10:00:00 | 1 | 2
分组
Code并
Block得到结果
