在多行中查找连续日期的开始和结束日期 [英] find start and stop date for contiguous dates in multiple rows
问题描述
在显示表格中的正确数据时遇到了一些麻烦. 我不太确定要搜索什么. 我不确定min(column)或max(column)在这里对我有帮助.让我们看看我能否解释我的问题.
I'm having a bit of trouble displaying correct data from my table. Im not really sure what to search for either. Im not sure min(column) or max(column) will help me here. Lets see if i can explain my problem.
我的表包含此数据:
> Code (nvarchar) | DateFrom (datetime) | DateTo (datetime)
> =========================================================
> 3006 | 2014-06-18 07:00:00 | 2014-06-18 08:00:00
> 3006 | 2014-06-18 09:00:00 | 2014-06-18 22:00:00
> 1006 | 2014-06-18 07:00:00 | 2014-06-18 09:00:00
> 1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00
> 1006 | 2014-06-18 08:10:00 | 2014-06-18 18:00:00
我将以观点介绍这一点. 它将按代码分组.
I am going to present this by a view. It will be grouped by Code.
我想要的是此输出:
> Code | DateFrom | DateTo
> =========================================================
> 3006 | 2014-06-08 07:00:00 | 2014-06-18 08:00:00
> 3006 | 2014-06-18 09:00:00 | 2014-06-18 22:00:00
> 1006 | 2014-06-18 07:00:00 | 2014-06-18 18:00:00
如您所见，DateTo和DateFrom之间是否存在间隙，我希望将其显示为两行.但是，如果下一个具有相同代码的"DateFrom"在DateTo结束之前(或同时)开始，则我希望改为显示"DateTo".
As you see if there are gaps between DateTo and DateFrom i want it to be presented as two rows. But if the next "DateFrom" with the same code begins before (or at same time) as DateTo ends, i want that "DateTo" to be shown instead.
在这种情况下，我看不到如何使用功能max()或min().由于时隙之间可能存在间隙.
I don't see how i could use the function max() or min() in this case. Because of the gaps that can be during the timeslots.
你们有什么线索吗?
我正在使用MS SQL 2012
Im using MS SQL 2012
提前谢谢！
如所评论.岛屿可能是我的祸根?
edit: as commented. Islands could be my solloution?
推荐答案
旧答案有一个缺点:每行仅与前一行进行检查，以验证时间段是否重叠，如果较早的行具有持续时间更长的时间段逻辑不会考虑它.例如:
The old answer had a weakness: every row is checked only with the previous to verify if the period are overlapping, if an earlier row have a period that last more the logic will not consider it. For example:
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并具有Code
To improve that PrevStop need to become LastStop and have the value of the max of the previous DateFrom for the 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) DateTo
FROM B
GROUP BY Code, Block
ORDER BY Code, Block
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING从MAX中删除当前行.
旧答案
此查询仅在不完全在先例内的每个期间都有效.
This query works only every the period is not completely inside the precedent.
这个想法是检查每一行是否与下一个/上一个链接.
如果链接了行，则它们形成一个块，它们将被分组在一起以获得第一个DateFrom和最后一个DateTo
The idea is to check for every row if it is linked with the next/previous one.
If rows are linked they form a block and they will be grouped together to get the first DateFrom and the last 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) DateTo
FROM B
GROUP BY Code, Block
ORDER BY Code, Block
SQLFiddle演示 ，其中添加了一些数据在同一天/天检查更多区块
SQLFiddle demo with some data added to check with more block on the same code/day
查询搜索块启动器，检查每行是代码的第一行(PrevStop IS NULL)还是在前一行(PrevStop < DateFrom)之外.
The query search for the block starter checking every row if they are the first for the code (PrevStop IS NULL) or if they are outside the previous one (PrevStop < DateFrom).
带窗口的SUM仅检索ORDER的前一行，以为链接数据块创建重要值，例如，我们将获得的测试数据
The windowed SUM retrieve only the previous row by the ORDER to create costant value for block of linked data, for example with the test data we will get
Code | DateStart | DateFrom | Starter | Block
-----+---------------------+---------------------+---------+------
1006 | 2014-06-18 07:00:00 | 2014-06-18 09:00:00 | 1 | 1
1006 | 2014-06-18 08:10:00 | 2014-06-18 06:00:00 | 0 | 1
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 | 0 | 1
1006 | 2014-06-18 07:00:00 | 2014-06-18 07:30:00 | 1 | 2
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 | 1 | 3
1006 | 2014-06-18 08:10:00 | 2014-06-18 09:00:00 | 0 | 3
3006 | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 | 1 | 1
3006 | 2014-06-18 09:00:00 | 2014-06-18 10:00:00 | 1 | 2
按Code和Block分组可得到结果
这篇关于在多行中查找连续日期的开始和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
