在T-SQL中计算相交时间间隔

Question

在T-SQL中计算相交时间间隔

sqlsql-servert-sqlsql-server-2012

6

代码：

CREATE TABLE #Temp1 (CoachID INT, BusyST DATETIME, BusyET DATETIME)
CREATE TABLE #Temp2 (CoachID INT, AvailableST DATETIME, AvailableET DATETIME)

INSERT INTO #Temp1 (CoachID, BusyST, BusyET)
SELECT 1,'2016-08-17 09:12:00','2016-08-17 10:11:00'
UNION
SELECT 3,'2016-08-17 09:30:00','2016-08-17 10:00:00'
UNION
SELECT 4,'2016-08-17 12:07:00','2016-08-17 13:10:00'

INSERT INTO #Temp2 (CoachID, AvailableST, AvailableET)
SELECT 1,'2016-08-17 09:07:00','2016-08-17 11:09:00'
UNION
SELECT 2,'2016-08-17 09:11:00','2016-08-17 09:30:00'
UNION
SELECT 3,'2016-08-17 09:24:00','2016-08-17 13:08:00'
UNION
SELECT 1,'2016-08-17 11:34:00','2016-08-17 12:27:00'
UNION
SELECT 4,'2016-08-17 09:34:00','2016-08-17 13:00:00'
UNION
SELECT 5,'2016-08-17 09:10:00','2016-08-17 09:55:00'

--RESULT-SET QUERY GOES HERE

DROP TABLE #Temp1
DROP TABLE #Temp2

期望输出结果:

CoachID CanCoachST                  CanCoachET                  NumOfCoaches
1       2016-08-17 09:12:00.000     2016-08-17 09:24:00.000     2 --(ID2 = 2,5)
1       2016-08-17 09:24:00.000     2016-08-17 09:30:00.000     3 --(ID2 = 2,3,5)
1       2016-08-17 09:30:00.000     2016-08-17 09:34:00.000     1 --(ID2 = 5)
1       2016-08-17 09:34:00.000     2016-08-17 09:55:00.000     2 --(ID2 = 4,5)
1       2016-08-17 09:55:00.000     2016-08-17 10:00:00.000     1 --(ID2 = 4)
1       2016-08-17 10:00:00.000     2016-08-17 10:11:00.000     2 --(ID2 = 3,4)
3       2016-08-17 09:30:00.000     2016-08-17 09:34:00.000     1 --(ID2 = 5)
3       2016-08-17 09:34:00.000     2016-08-17 09:55:00.000     2 --(ID2 = 4,5)
3       2016-08-17 09:55:00.000     2016-08-17 10:00:00.000     1 --(ID2 = 4)
4       2016-08-17 12:07:00.000     2016-08-17 12:27:00.000     2 --(ID2 = 1,3)
4       2016-08-17 12:27:00.000     2016-08-17 13:08:00.000     1 --(ID2 = 3)
4       2016-08-17 13:08:00.000     2016-08-17 13:10:00.000     0 --(No one is available)

目标：将#Temp1视为团队教练（ID1）及其会议时间（ST1 =会议开始时间和ET1 =会议结束时间）的表格。将#Temp2视为团队教练（ID2）及其总可用时间（ST2 =可用开始时间和ET2 =可用结束时间）的表格。

现在，目标是找到所有可能的来自#Temp2的教练，他们可以在#Temp1中的教练的会议时间内担任教练。

例如，对于教练ID1 = 1，在9:12到10:11之间忙碌的情况下（如果该信息跨越多天，则数据可以跨越多天），我们有以下情况：教练ID2 = 2和5可以在9:12到9:24之间担任教练，教练ID2 = 2、3和5可以在9:24到9:30之间担任教练，教练ID2 = 5可以在9:30到9:34之间担任教练，教练ID2 = 4和5可以在9:34到9:55之间担任教练，教练ID2 = 4可以在9:55到10:00之间担任教练，教练ID2 = 3和4可以在10:00到10:11之间担任教练（请注意，ID 3虽然在#Temp2表格中在9:24到13:08之间可用，但它不能在9:24到10:00之间为ID1 = 1担任教练，因为它还在9:30到10:00之间忙碌）。

我的努力：目前只涉及到分解#Temp1的时间范围。仍需要弄清楚A）如何从输出中删除那些非繁忙的时间窗口B）添加一个字段/将其映射到正确的T1的教练ID。

;WITH ED
AS (SELECT BusyET, CoachID FROM #Temp1  
    UNION ALL   
    SELECT BusyST, CoachID FROM #Temp1
    )
,Brackets
AS (SELECT MIN(BusyST) AS BusyST
        ,(  SELECT MIN(BusyET)
            FROM ED e
            WHERE e.BusyET > MIN(BusyST)
            ) AS BusyET
    FROM #Temp1 T   
    UNION ALL   
    SELECT B.BusyET
        ,e.BusyET
    FROM Brackets B
    INNER JOIN ED E ON B.BusyET < E.BusyET
    WHERE NOT EXISTS (
            SELECT *
            FROM ED E2
            WHERE E2.BusyET > B.BusyET
                AND E2.BusyET < E.BusyET
            )
    )
SELECT *
FROM Brackets
ORDER BY BusyST;

我认为需要比较两个表之间的ST/ET日期来加入，其中ID彼此不匹配。但我在弄清楚如何仅获取会议时间窗口和唯一计数方面遇到了麻烦。

更新了更好的架构/数据集。还要注意，即使教练ID 4没有“安排”可用，他仍然被列为忙碌状态，持续了最后几分钟。并且可能存在其他人在那段时间内无法工作的情况，在这种情况下，我们可以返回0个记录（或者如果真的很复杂，则不返回它）。

再次强调，目标是找到所有可用教练ID及其可用时间窗口的数量和组合，以便指导忙碌表中列出的教练ID。

使用与样本数据相匹配的更多示例说明进行了更新。

- 007

你提供的示例数据有些混乱。你说只有ID2的2、3、5可以填补ID1的前半小时，但是你的示例数据显示ID2 1在那个时间也是可用的...与预期输出相比，其余示例数据同样令人困惑... - ZLK

1

我认为#Temp2只是每个教练的工作时间/班次。 - cha

@ZLK，是的，TT1是教练会议时间，TT2是教练可能的工作时间。因此，当我们返回ID1 = 1的结果集时，我们不应该返回ID2 = 1的TT2中的那个时间。 - 007

你是否有一个最小的时间间隔或持续时间，想要为某个人安排日程？例如30分钟或15分钟？ - john.da.costa

1

@JohnDaCosta，没有最小/最大/设置持续时间。 :( - 007

@007 我猜如果你每天运行它，并且只使用4字节的整数，按秒进行调度，那么在我下面的设计中，每个教练每天在事实表上只会得到4字节 * 4列 * 86400秒 --> （1.32MB）系统中每个教练每天在事实表上的存储空间。 - john.da.costa

7个回答

1

据我所知，您想要的是类似于这样的东西：

;WITH CTE AS (
    SELECT ID1, ST1, DATEADD(MINUTE, 30, ST1) ET1
    FROM #Temp1
    UNION ALL
    SELECT C.ID1, C.ET1, DATEADD(MINUTE, 30, C.ET1)
    FROM CTE C
    JOIN #Temp1 T ON T.ID1 = C.ID1
    WHERE T.ET1 >= DATEADD(MINUTE, 30, C.ET1))
SELECT *
FROM CTE C
OUTER APPLY (
    SELECT COUNT(*) ID2Cnt
    FROM #Temp2 T
    WHERE ST2 <= C.ST1
    AND ET2 >= C.ET1
    AND ID2 <> C.ID1
    AND NOT EXISTS (
        SELECT 1
        FROM CTE
        WHERE ID1 = T.ID2
        AND ST1 <= C.ST1
        AND ET1 >= C.ET1)) T
ORDER BY ID1, ST1;

CTE将把您的＃Temp1教练分成半小时的时间段，然后我假设您想找到＃Temp2中所有不是相同ID并且开始时间早于或同时开始并且结束时间晚于或同时结束的人... 注意: 我假设这里的块只能是半小时。

编辑：算了吧...我刚意识到您还想从结果集中排除＃Temp1中繁忙的人，所以我在应用程序中添加了一个不存在的子句...

- ZLK

很快会检查，但问题是这些块可以是任意分钟数..:( - 007

@007 如果不是半小时块，您将如何决定将有哪种块？它们是否总是在半小时点结束/开始？（例如，如果某人忙于9:15到9:40，您会寻找一个15分钟和一个10分钟的块吗？还是只需要一个25分钟的块？） - ZLK

已更新原帖附带更好的数据集。基本上，我们需要查看繁忙表（#Temp2）中的所有教练ID及其繁忙时间，并找到那些既不忙碌（不在#Temp1中），又有工作空闲时间（在#Temp2中）的教练的时间窗口组合及其计数。是的，只要可以计算出额外的教练数量并反过来，时间就会分成每个时间窗口组合。这有帮助吗？ - 007

1

这个查询将进行计算：

SELECT TT1.ID1
 , case when TT2.ST2 < TT1.ST1 THEN TT1.ST1 ELSE TT2.ST2 END
 , case when TT2.ET2 > TT1.ET1 THEN TT1.ET1 ELSE TT2.ET2 END
 , COUNT(distinct TT2.id2) 
FROM #Temp1 TT1 INNER JOIN #Temp2 TT2
ON TT1.ET1 > TT2.ST2 AND TT1.ST1 < TT2.ET2 AND TT1.ID1 <> TT2.ID2
GROUP BY TT1.ID1
 , case when TT2.ST2 < TT1.ST1 THEN TT1.ST1 ELSE TT2.ST2 END
 , case when TT2.ET2 > TT1.ET1 THEN TT1.ET1 ELSE TT2.ET2 END

然而，结果将包括教练可以填补整个时间段的空缺，例如对于教练1，将有三个时间段：从9:00到9:30由替代教练#2填补，从9:30到10:00由替代教练#4填补，以及从9:00到10:00的时间段由替代教练#3和#4填补。以下是完整的结果：

ID1                                                         
----------- ----------------------- ----------------------- -----------
1           2016-08-17 09:00:00.000 2016-08-17 09:30:00.000 1
1           2016-08-17 09:00:00.000 2016-08-17 10:00:00.000 2
1           2016-08-17 09:30:00.000 2016-08-17 10:00:00.000 1
3           2016-08-17 09:30:00.000 2016-08-17 10:00:00.000 3
4           2016-08-17 12:00:00.000 2016-08-17 12:30:00.000 1
4           2016-08-17 12:00:00.000 2016-08-17 13:00:00.000 1

- cha

感谢@cha的贡献。唯一的问题是，部分/完整计数可能会引起混淆，因此我们需要获取唯一的部分计数。 - 007

1

这是您期望的结果，考虑到重叠的可用教练和繁忙的教练。

| CoachID | CanCoachST       | CanCoachET       | NumOfCoaches | CanCoach |
|---------|------------------|------------------|--------------|----------|
| 1       | 2016-08-17 09:12 | 2016-08-17 09:24 | 2            | 2, 5     |
| 1       | 2016-08-17 09:24 | 2016-08-17 09:30 | 3            | 2, 3, 5  |
| 1       | 2016-08-17 09:30 | 2016-08-17 09:34 | 1            | 5        |
| 1       | 2016-08-17 09:34 | 2016-08-17 09:55 | 2            | 4, 5     |
| 1       | 2016-08-17 09:55 | 2016-08-17 10:00 | 1            | 4        |
| 1       | 2016-08-17 10:00 | 2016-08-17 10:11 | 2            | 3, 4     |
| 3       | 2016-08-17 09:30 | 2016-08-17 09:34 | 1            | 5        |
| 3       | 2016-08-17 09:34 | 2016-08-17 09:55 | 2            | 4, 5     |
| 3       | 2016-08-17 09:55 | 2016-08-17 10:00 | 1            | 4        |
| 4       | 2016-08-17 12:07 | 2016-08-17 12:27 | 2            | 1, 3     |
| 4       | 2016-08-17 12:27 | 2016-08-17 13:08 | 1            | 3        |
| 4       | 2016-08-17 13:08 | 2016-08-17 13:10 | 0            | NULL     |

#Temp1 作为繁忙的教练：

| CoachID | BusyST           | BusyET           |
|---------|------------------|------------------|
| 1       | 2016-08-17 09:12 | 2016-08-17 10:11 |
| 3       | 2016-08-17 09:30 | 2016-08-17 10:00 |
| 4       | 2016-08-17 12:07 | 2016-08-17 13:10 |

#Temp2作为可用教练：

| CoachID | AvailableST      | AvailableET      |
|---------|------------------|------------------|
| 1       | 2016-08-17 09:07 | 2016-08-17 11:09 |
| 1       | 2016-08-17 11:34 | 2016-08-17 12:27 |
| 2       | 2016-08-17 09:11 | 2016-08-17 09:30 |
| 3       | 2016-08-17 09:24 | 2016-08-17 13:08 |
| 4       | 2016-08-17 09:34 | 2016-08-17 13:00 |
| 5       | 2016-08-17 09:10 | 2016-08-17 09:55 |

下面的脚本有点长。

;
with
st 
(
    CoachID,
    CanCoachST
)
as
(
    select 
        bound.CoachID,
        s.BusyST 
    from 
        #Temp1 as s 
    cross apply
    (
        select
            b.CoachID,
            b.BusyST,
            b.BusyET
        from
            #Temp1 as b
        where 1 = 1
        and s.BusyST between b.BusyST and b.BusyET
    )
    as bound

    union all

    select 
        bound.CoachID,
        s.BusyET 
    from 
        #Temp1 as s 
    cross apply
    (
        select
            b.CoachID,
            b.BusyST,
            b.BusyET
        from
            #Temp1 as b
        where 1 = 1
        and s.BusyET between b.BusyST and b.BusyET
        and s.CoachID != b.CoachID
    )
    as bound

    union all

    select 
        bound.CoachID,
        s.AvailableST 
    from 
        #Temp2 as s 
    cross apply
    (
        select
            b.CoachID,
            b.BusyST,
            b.BusyET
        from
            #Temp1 as b
        where 1 = 1
        and s.AvailableST between b.BusyST and b.BusyET
    )
    as bound

    union all

    select 
        bound.CoachID,
        s.AvailableET 
    from 
        #Temp2 as s 
    cross apply
    (
        select
            b.CoachID,
            b.BusyST,
            b.BusyET
        from
            #Temp1 as b
        where 1 = 1
        and s.AvailableET between b.BusyST and b.BusyET
        and s.CoachID != b.CoachID
    )
    as bound
),
d as
(
    select distinct
        CoachID,
        CanCoachST
    from
        st
),
r as
(
    select
        row_number() over (order by CoachID, CanCoachST) as RowID,
        CoachID,
        CanCoachST
    from
        d
),
rng as
(
    select
        r1.RowID,
        r1.CoachID,
        r1.CanCoachST,
        case when r1.CoachID = r2.CoachID 
            then r2.CanCoachST else t.BusyET end as CanCoachET
    from
        r as r1
    left join
        r as r2
    on
        r1.RowID = r2.RowID - 1
    left join
        #Temp1 as t
    on
        t.CoachID = r1.CoachID
),
c as
(
    select
        rng.RowID,
        rng.CoachID,
        rng.CanCoachST,
        rng.CanCoachET,
        t2.CoachID as CanCoachID
    from
        rng
    cross join
        #Temp1 as t1
    cross join
        #Temp2 as t2
    where 1 = 1
    and t2.CoachID != rng.CoachID
    and t2.AvailableST <= rng.CanCoachST
    and t2.AvailableET >= rng.CanCoachET
),
b as
(
    select
        rng.RowID,
        rng.CoachID,
        rng.CanCoachST,
        rng.CanCoachET,
        t1.CoachID as BusyCoachID
    from
        rng
    cross join
        #Temp1 as t1
    where 1 = 1
    and t1.CoachID != rng.CoachID
    and t1.BusyST <= rng.CanCoachST
    and t1.BusyET >= rng.CanCoachET
),
e as
(
    select
        c.RowID,
        c.CoachID,
        c.CanCoachST,
        c.CanCoachET,
        c.CanCoachID
    from
        c

    except

    select
        b.RowID,
        b.CoachID,
        b.CanCoachST,
        b.CanCoachET,
        b.BusyCoachID
    from
        b
),
f as
(
    select
        rng.RowID,
        rng.CoachID,
        rng.CanCoachST,
        rng.CanCoachET,
        e.CanCoachID
    from
        rng
    left join   
        e
    on
        e.RowID = rng.RowID
)
select
    f.CoachID,
    f.CanCoachST,
    f.CanCoachET,
    count(f.CanCoachID) as NumOfCoaches,
    stuff
    (
        (
            select ', ' + cast(f1.CanCoachID as varchar(5))
                from f as f1 where f1.RowID = f.RowID
                for xml path('')
        ), 
        1, 2, ''
    )
    as CanCoach
from
    f
group by
    f.RowID,
    f.CoachID,
    f.CanCoachST,
    f.CanCoachET
order by
    1, 2

- Irawan Soetomo

非常感谢您的输入。但是与 Vlad 类似，我发现教练 ID 3 在 9:30 到 10:00 之间显示可用于代替教练 ID 1，但实际上不应该是这样（请参考我对 Vlad 帖子的评论）。除此之外，已经非常接近了。谢谢。 - 007

1

我建议使用时间间隔/时间段表的概念。另一种解释是考虑一个“时间维度表”。定义所有的时间，然后以你关心的粒度记录与时间间隔有关的事实。因为你的时间以7和11分钟结束，所以我选择了1分钟间隔，但我建议使用15-30分钟间隔。通过这样做，可以轻松地连接/比较表格。考虑以下设计/实现： - 维度表

-- drop table #intervals
create table #intervals(intervalId  int identity(1,1) not null primary key clustered,intervalStartTime datetime unique)
declare @s datetime, @e datetime, @i int 
set @s = '2016-08-16'
set @e = '2016-08-18'
set @i = 1
while (@s <= @e )
begin 
 insert into #intervals(intervalStartTime) values(@s)
 set @s = dateadd(minute, @i, @s)
end

-- 事实表：

-- drop table #Fact

创建一个名为#Fact的表，包含四个列：intervalId、coachid、isBusy（默认值为0）和isAvailable（默认值为0）。

-- 记录每位教练的时间

   insert into #Fact(coachid,intervalId)
select distinct c.coachid, i.intervalId  
from 
(
select distinct coachid from #temp1
union
select distinct coachid from #temp2
) c cross join #intervals i

-- record free / busy info
update f set isbusy = 1 
from #intervals i inner join #fact f on i.intervalId  = f.intervalId  
inner join #temp1 t on f.coachid = t.coachid and i.intervalStartTime  between t.BusyST and t.BusyET

    -- record free / busy info
update f set isAvailable = 1 
from #intervals i inner join #fact f on i.intervalId  = f.intervalId  
inner join #temp2 t on f.coachid = t.coachid and i.intervalStartTime  between t.AvailableST and t.AvailableET

-- 构建您的查询以查找共同时间等。

select * from #intervals i inner join #Fact f on i.intervalId = f.intervalId

-- 显示可用教练与空闲教练数量的示例结果

选择 i.intervalId，i.intervalStartTime，sum(isBusy) 作为 coachesBusy，sum(isAvailable) 作为 coachesAvailable 从 #intervals i 内连接 #Fact f on i.intervalId = f.intervalId 按 i.intervalId，i.intervalStartTime 分组当 sum(isBusy) < sum(isAvailable) 时

你可以按照需要查找常见或唯一的间隔ID。如果需要进一步澄清，请告诉我。

- john.da.costa

我建议使用15分钟或30分钟的时间间隔，因为我不认为有任何一种辅导可以在少于15分钟的时间内完成。根据您示例中的数据，我选择了1分钟的粒度，但我认为这太详细了，除非您有特殊的需求或关注。 - john.da.costa

1

我正在使用一个小的数字表格...你不需要针对日期做任何处理，只需要用数字。我正在构建的东西比实际场景中使用的要小。

CREATE TABLE dbo.Numbers (Num INT PRIMARY KEY CLUSTERED);

WITH E1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))     AS t(N))
,E2 AS (SELECT N = 1 FROM E1 AS a, E1 AS b)
,E4 AS (SELECT N = 1 FROM E2 AS a, E2 AS b)
,cteTally AS (SELECT N = 0 UNION ALL
                SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM     E4)
INSERT INTO dbo.Numbers (Num)
SELECT N FROM cteTally;

请注意下面的@startDate...它人为地靠近你处理的日期，在真实的生产场景中，你需要将该日期提前以配合更大的Numbers表。

以下是解决方案，它适用于旧版SQL Server（以及你标记的2012版本）。

DECLARE @startDate DATETIME = '20160817';
WITH cteBusy AS
(
SELECT  num.Num
    ,   busy.CoachID
FROM #Temp1 AS busy
JOIN dbo.Numbers AS num
    ON num.Num >= DATEDIFF(MINUTE, @startDate, busy.BusyST)
    AND num.Num < DATEDIFF(MINUTE, @startDate, busy.BusyET)
)
, cteAvailable AS
(
SELECT  num.Num
    ,   avail.CoachID
FROM #Temp2 AS avail
JOIN dbo.Numbers AS num
    ON num.Num >= DATEDIFF(MINUTE, @startDate, avail.AvailableST)
    AND num.Num < DATEDIFF(MINUTE, @startDate, avail.AvailableET)
LEFT JOIN cteBusy AS b
    ON b.Num = num.Num
    AND b.CoachID = avail.CoachID
WHERE b.Num IS NULL 
)
,   cteGrouping AS
(
SELECT  b.Num
    ,   b.CoachID
    ,   NumOfCoaches = COUNT(a.CoachID)
FROM cteBusy AS b
LEFT JOIN cteAvailable AS a
    ON a.Num = b.Num
GROUP BY b.Num, b.CoachID
)
,   cteFinal AS
(
SELECT  cte.Num
    ,   cte.CoachID
    ,   cte.NumOfCoaches
    ,   block = cte.Num - ROW_NUMBER() OVER(PARTITION BY cte.CoachID, cte.NumOfCoaches ORDER BY cte.Num)
FROM cteGrouping AS cte
)
SELECT  cte.CoachID
,   CanCoachST = DATEADD(MINUTE, MIN(cte.Num), @startDate)
,   CanCoachET = DATEADD(MINUTE, MAX(cte.Num) + 1, @startDate)
,   cte.NumOfCoaches
FROM cteFinal AS cte
GROUP BY cte.CoachId, cte.NumOfCoaches, cte.block
ORDER BY cte.CoachID, CanCoachST;

享受！

- btberry

看起来像是一种类似于计数表的方法..有趣！感谢@btberry提供这个建议。干杯 - 007

它完全按照你的要求进行操作，并且性能比你选择的正确答案要高得多。你的选择标准非常奇怪。 - btberry

1

我相信以下查询语句可以工作，但无法保证性能。

CREATE TABLE #Temp1 (CoachID INT, BusyST DATETIME, BusyET DATETIME)
CREATE TABLE #Temp2 (CoachID INT, AvailableST DATETIME, AvailableET DATETIME)

INSERT INTO #Temp1 (CoachID, BusyST, BusyET)
SELECT 1,'2016-08-17 09:12:00','2016-08-17 10:11:00'
UNION
SELECT 3,'2016-08-17 09:30:00','2016-08-17 10:00:00'
UNION
SELECT 4,'2016-08-17 12:07:00','2016-08-17 13:10:00'

INSERT INTO #Temp2 (CoachID, AvailableST, AvailableET)
SELECT 1,'2016-08-17 09:07:00','2016-08-17 11:09:00'
UNION
SELECT 2,'2016-08-17 09:11:00','2016-08-17 09:30:00'
UNION
SELECT 3,'2016-08-17 09:24:00','2016-08-17 13:08:00'
UNION
SELECT 1,'2016-08-17 11:34:00','2016-08-17 12:27:00'
UNION
SELECT 4,'2016-08-17 09:34:00','2016-08-17 13:00:00'
UNION
SELECT 5,'2016-08-17 09:10:00','2016-08-17 09:55:00'


;WITH WorkScheduleWithID    -- Select work schedule (#Temp2 – available times) and generate ID for each schedule entry. 
AS
(
    SELECT   ROW_NUMBER() OVER (ORDER BY [CoachID]) AS [ID]
            ,[WS].[CoachID]
            ,[WS].[AvailableST] AS [Start]
            ,[WS].[AvailableET] As [End]
    FROM    #Temp2 [WS]
), SchedulesIntersect -- Determine where work schedule and meeting schedule (busy times) intersect.
AS
(
    SELECT   [ID]
            ,[CoachID]
            ,[Start]
            ,[End]
            ,[IntersectTime]
            ,SUM([Availability]) OVER (PARTITION BY [ID] ORDER BY [IntersectTime]) AS GroupID
    FROM    (
                SELECT       [WS].[ID]
                            ,[WS].[CoachID]
                            ,[WS].[Start]
                            ,[WS].[End]
                            ,[MS1].[BusyST] AS [IntersectTime]
                            ,0 AS [Availability]
                FROM        WorkScheduleWithID [WS]
                INNER JOIN  #Temp1 [MS1] ON ([MS1].[CoachID] = [WS].[CoachID]) 
                                                        AND
                                                     ( ([MS1].[BusyST] > [WS].[Start]) AND ([MS1].[BusyST] < [WS].[End]) ) -- Meeting start contained with in work schedule
                UNION ALL
                SELECT       [WS].[ID]
                            ,[WS].[CoachID]
                            ,[WS].[Start]
                            ,[WS].[End]
                            ,[MS2].[BusyET] AS [IntersectTime]
                            ,1 AS [Availability]
                FROM        WorkScheduleWithID [WS]
                INNER JOIN  #Temp1 [MS2] ON ([MS2].[CoachID] = [WS].[CoachID]) 
                                                        AND
                                                     ( ([MS2].BusyET > [WS].[Start]) AND ([MS2].BusyET < [WS].[End]) )  -- Meeting end contained with in work schedule
            ) Intersects
),ActualAvailability -- Determine actual availability of each coach based on work schedule and busy time.
AS
(
    SELECT   [ID]
            ,[CoachID]
            ,(
                CASE
                    WHEN [GroupID] = 0 THEN [Start]
                    ELSE MIN([IntersectTime]) 
                END
             ) AS [Start]
            ,(
                CASE
                    WHEN ( ([GroupID] > 0) AND (MIN([IntersectTime]) = MAX([IntersectTime])) ) THEN [End]
                    ELSE MAX([IntersectTime]) 
                END
             ) AS [End]
    FROM    SchedulesIntersect
    GROUP BY [ID], [CoachID], [Start], [End], [GroupID]
    UNION ALL
    SELECT  [ID]
            ,[CoachID]
            ,[Start]
            ,[End]
    FROM    WorkScheduleWithID WS
    WHERE   WS.ID NOT IN (SELECT ID FROM SchedulesIntersect)
),TimeIntervals -- Determine time intervals for which each coach’s availability will be checked against.
AS
(
    SELECT DISTINCT *
    FROM    (
                SELECT       MS.CoachID
                            ,MS.BusyST
                            ,MS.BusyET
                            ,(
                                CASE
                                    WHEN AC.Start < MS.BusyST THEN  MS.BusyST 
                                    ELSE AC.Start
                                END
                             )  AS [TS]
                FROM        #Temp1 MS
                LEFT OUTER JOIN ActualAvailability AC ON (AC.CoachID <> MS.CoachID) 
                            AND 
                            (
                                ( (MS.[BusyST] <= AC.[Start]) AND (MS.[BusyET] >= AC.[End]) ) OR                                -- Meeting covers entire work schedule 
                                ( (MS.[BusyST] > AC.[Start]) AND (MS.[BusyET] < AC.[End]) ) OR                              -- Meeting is contained with in work schedule
                                ( (MS.[BusyST] < AC.[Start]) AND (MS.[BusyET] > AC.[Start])  AND ([MS].[BusyET] < AC.[End]) ) OR   -- Meeting ends within work schedule (partial overlap)
                                ( (MS.[BusyST] > AC.[Start]) AND (MS.[BusyST] < AC.[End])  AND ([MS].[BusyET] > AC.[End]) ) -- Meeting starts within work schedule (partial overlap)
                            ) 
                UNION ALL
                SELECT       MS.CoachID
                            ,MS.BusyST
                            ,MS.BusyET
                            ,(
                                CASE
                                    WHEN AC.[End] > MS.BusyET THEN  MS.BusyET 
                                    ELSE AC.[End]
                                END
                             )  AS [TS]
                FROM        #Temp1 MS
                LEFT OUTER JOIN ActualAvailability AC ON (AC.CoachID <> MS.CoachID) 
                            AND 
                            (
                                ( (MS.[BusyST] <= AC.[Start]) AND (MS.[BusyET] >= AC.[End]) ) OR                                -- Meeting covers entire work schedule 
                                ( (MS.[BusyST] > AC.[Start]) AND (MS.[BusyET] < AC.[End]) ) OR                              -- Meeting is contained with in work schedule
                                ( (MS.[BusyST] < AC.[Start]) AND (MS.[BusyET] > AC.[Start])  AND ([MS].[BusyET] < AC.[End]) ) OR   -- Meeting ends within work schedule (partial overlap)
                                ( (MS.[BusyST] > AC.[Start]) AND (MS.[BusyST] < AC.[End])  AND ([MS].[BusyET] > AC.[End]) ) -- Meeting starts within work schedule (partial overlap)
                            ) 
            ) Intervals
),AvailableCoachTimeSegments -- Determine each coach’s availability against each time interval being checked.
AS
(
    SELECT          ROW_NUMBER() OVER (PARTITION BY TI.CoachID ORDER BY TI.Start, AT.CoachID) AS RankAsc
                    ,ROW_NUMBER() OVER (PARTITION BY TI.CoachID ORDER BY TI.[End] DESC, AT.CoachID DESC) AS RankDesc
                    ,TI.CoachID
                    ,TI.BusyST
                    ,TI.BusyET
                    ,TI.Start
                    ,TI.[End]
                    ,AT.CoachID AS AvailableCoachID
                    ,AT.Start AS AvailableStart
                    ,AT.[End] AS AvailableEnd
                    ,(
                        CASE
                            WHEN (MIN(TI.[Start]) OVER (PARTITION BY TI.CoachID)) <> TI.BusyST THEN 1
                            ELSE 0
                        END
                     ) AS StartIncomplete
                    ,(
                        CASE
                            WHEN (MAX(TI.[End]) OVER (PARTITION BY TI.CoachID)) <> TI.BusyET THEN 1
                            ELSE 0
                        END
                     ) AS EndIncomplete
    FROM            (
                        SELECT   CoachID
                                ,BusyST
                                ,BusyET
                                ,TS AS [Start]
                                ,LEAD(TS, 1, TS) OVER (PARTITION BY CoachID ORDER BY TS) AS [End]
                        FROM    TimeIntervals
                    ) TI    
    LEFT OUTER JOIN  ActualAvailability AT ON 

                                (
                                    ( (AT.[Start] <= TI.[Start]) AND (AT.[End] >= TI.[End]) ) OR                                -- Coach availability covers entire time segment 
                                    ( (AT.[Start] > TI.[Start]) AND (AT.[End] < TI.[End]) ) OR                                  -- Coach availability is contained within the time segment 
                                    ( (AT.[Start] < TI.[Start]) AND (AT.[End] > TI.[Start])  AND (AT.[End] < TI.[End]) ) OR     -- Coach availability ends within the time segment (partial overlap)
                                    ( (AT.[Start] > TI.[Start]) AND (AT.[Start] < TI.[End])  AND (AT.[End] > TI.[End]) )        -- Coach availability starts within the time segment (partial overlap)
                                )  
)
-- Final result 
SELECT  CoachID
        ,BusyST
        ,BusyET
        ,Start AS CanCoachST
        ,[End] AS CanCoachET
        ,COUNT(AvailableCoachID) AS NumOfCoaches
         ,ISNULL(STUFF((
                    SELECT TOP 100 PERCENT ', ' + CAST(AvailableCoach.AvailableCoachID  AS VARCHAR(MAX))
                    FROM AvailableCoachTimeSegments  AvailableCoach
                    WHERE (AvailableCoach.CoachID = Results.CoachID AND AvailableCoach.Start = Results.Start AND  AvailableCoach.[End] = Results.[End]) 
                    ORDER BY AvailableCoach.AvailableCoachID
                    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
                  ,1,2,''), '(No one is available)') AS AvailableCoaches
FROM    AvailableCoachTimeSegments Results
WHERE   [Start] <> [End]
GROUP   BY CoachID, BusyST, BusyET, Start, [End], StartIncomplete, EndIncomplete
UNION ALL -- Add any missing time segments at the start of the busy time or end of the busy time. 
SELECT  CoachID
        ,BusyST
        ,BusyET
        ,(
            CASE 
                WHEN StartIncomplete = 1 THEN  BusyST
                WHEN EndIncomplete = 1 THEN MAX([End])
                ELSE Start
            END
        ) AS CanCoachST
        ,(
            CASE 
                WHEN StartIncomplete = 1 THEN  Start
                WHEN EndIncomplete = 1 THEN BusyET
                ELSE [End]
            END
        ) AS CanCoachET
        ,0 AS NumOfCoaches
        ,'(No one is available)' AS AvailableCoaches
FROM    AvailableCoachTimeSegments Results
WHERE   [Start] <> [End] AND ( (StartIncomplete = 1 AND RankAsc = 1) OR (EndIncomplete = 1 AND RankDesc = 1) ) 
GROUP   BY CoachID, BusyST, BusyET, Start, [End], StartIncomplete, EndIncomplete
ORDER BY CoachID, CanCoachST


DROP TABLE #Temp1
DROP TABLE #Temp2

- Edmond Quinton

这也可以（格式也很好）。对，性能是我真正需要考虑的问题，因为这个查询将针对一个非常庞大的数据集而且经常运行。再次感谢@EdmondQuinton提供的解决方案。 - 007

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vladimir Baranov · Accepted Answer

这个答案中的查询受到了Itzik Ben-Gan的Packing Intervals的启发。

起初，我没有完全理解要求的复杂性，并假设Table1和Table2中的间隔不重叠。我认为同一教练不能同时忙碌和可用。

事实证明我的假设是错误的，因此我留下的第一个变体查询必须扩展预处理步骤，从Table1存储的所有间隔中减去存储在Table2中的间隔。

它使用类似的想法。每个“可用”时间间隔的开始都用+1 EventType标记，结束则用-1 EventType标记。对于“忙碌”间隔，标记被反转。“忙碌”间隔以-1开始，以+1结束。这在C1_Subtract中完成。

然后运行总数告诉我们“真正”可用的时间间隔在哪里（C2_Subtract）。最后，CTE_Available只保留“真正”可用的时间间隔。 示例数据 我添加了几行来说明如果没有教练可用会发生什么。我还添加了CoachID=9，它不在第一个变体查询的初始结果中。

CREATE TABLE #Temp1 (CoachID INT, BusyST DATETIME, BusyET DATETIME);
CREATE TABLE #Temp2 (CoachID INT, AvailableST DATETIME, AvailableET DATETIME);
-- Start time is inclusive
-- End time is exclusive

INSERT INTO #Temp1 (CoachID, BusyST, BusyET) VALUES
(1, '2016-08-17 09:12:00','2016-08-17 10:11:00'),
(3, '2016-08-17 09:30:00','2016-08-17 10:00:00'),
(4, '2016-08-17 12:07:00','2016-08-17 13:10:00'),

(6, '2016-08-17 15:00:00','2016-08-17 16:00:00'),
(9, '2016-08-17 15:00:00','2016-08-17 16:00:00');

INSERT INTO #Temp2 (CoachID, AvailableST, AvailableET) VALUES
(1,'2016-08-17 09:07:00','2016-08-17 11:09:00'),
(2,'2016-08-17 09:11:00','2016-08-17 09:30:00'),
(3,'2016-08-17 09:24:00','2016-08-17 13:08:00'),
(1,'2016-08-17 11:34:00','2016-08-17 12:27:00'),
(4,'2016-08-17 09:34:00','2016-08-17 13:00:00'),
(5,'2016-08-17 09:10:00','2016-08-17 09:55:00'),

(7,'2016-08-17 15:10:00','2016-08-17 15:20:00'),
(8,'2016-08-17 15:15:00','2016-08-17 15:25:00'),
(7,'2016-08-17 15:40:00','2016-08-17 15:55:00'),
(9,'2016-08-17 15:05:00','2016-08-17 15:07:00'),
(9,'2016-08-17 15:40:00','2016-08-17 16:55:00');

< p > CTE_Available的中间结果

+---------+-------------------------+-------------------------+
| CoachID |       AvailableST       |       AvailableET       |
+---------+-------------------------+-------------------------+
|       1 | 2016-08-17 09:07:00.000 | 2016-08-17 09:12:00.000 |
|       1 | 2016-08-17 10:11:00.000 | 2016-08-17 11:09:00.000 |
|       1 | 2016-08-17 11:34:00.000 | 2016-08-17 12:27:00.000 |
|       2 | 2016-08-17 09:11:00.000 | 2016-08-17 09:30:00.000 |
|       3 | 2016-08-17 09:24:00.000 | 2016-08-17 09:30:00.000 |
|       3 | 2016-08-17 10:00:00.000 | 2016-08-17 13:08:00.000 |
|       4 | 2016-08-17 09:34:00.000 | 2016-08-17 12:07:00.000 |
|       5 | 2016-08-17 09:10:00.000 | 2016-08-17 09:55:00.000 |
|       7 | 2016-08-17 15:10:00.000 | 2016-08-17 15:20:00.000 |
|       7 | 2016-08-17 15:40:00.000 | 2016-08-17 15:55:00.000 |
|       8 | 2016-08-17 15:15:00.000 | 2016-08-17 15:25:00.000 |
|       9 | 2016-08-17 16:00:00.000 | 2016-08-17 16:55:00.000 |
+---------+-------------------------+-------------------------+

现在我们可以使用这些CTE_Available的中间结果，而不是在第一个查询变体中使用#Temp2。请参见查询的第一个变体下面的详细说明。

完整查询：

WITH
C1_Subtract
AS
(
    SELECT
        CoachID
        ,AvailableST AS ts
        ,+1 AS EventType
    FROM #Temp2

    UNION ALL

    SELECT
        CoachID
        ,AvailableET AS ts
        ,-1 AS EventType
    FROM #Temp2

    UNION ALL

    SELECT
        CoachID
        ,BusyST AS ts
        ,-1 AS EventType
    FROM #Temp1

    UNION ALL

    SELECT
        CoachID
        ,BusyET AS ts
        ,+1 AS EventType
    FROM #Temp1
)
,C2_Subtract AS
(
    SELECT
        C1_Subtract.*
        ,SUM(EventType)
            OVER (
            PARTITION BY CoachID
            ORDER BY ts, EventType DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS cnt
        ,LEAD(ts) 
            OVER (
            PARTITION BY CoachID
            ORDER BY ts, EventType DESC)
        AS NextTS
    FROM C1_Subtract
)
,CTE_Available
AS
(
    SELECT
        C2_Subtract.CoachID
        ,C2_Subtract.ts AS AvailableST
        ,C2_Subtract.NextTS AS AvailableET
    FROM C2_Subtract
    WHERE cnt > 0
)
,CTE_Intervals
AS
(
    SELECT
        TBusy.CoachID AS BusyCoachID
        ,TBusy.BusyST
        ,TBusy.BusyET
        ,CA.CoachID AS AvailableCoachID
        ,CA.AvailableST
        ,CA.AvailableET
        -- max of start time
        ,CASE WHEN CA.AvailableST < TBusy.BusyST
        THEN TBusy.BusyST
        ELSE CA.AvailableST 
        END AS ST
        -- min of end time
        ,CASE WHEN CA.AvailableET > TBusy.BusyET
        THEN TBusy.BusyET
        ELSE CA.AvailableET
        END AS ET
    FROM
        #Temp1 AS TBusy
        CROSS APPLY
        (
            SELECT
                TAvailable.*
            FROM
                CTE_Available AS TAvailable
            WHERE
                -- the same coach can't be available and busy
                TAvailable.CoachID <> TBusy.CoachID
                -- intervals intersect
                AND TAvailable.AvailableST < TBusy.BusyET
                AND TAvailable.AvailableET > TBusy.BusyST
        ) AS CA
)
,C1 AS
(
    SELECT
        BusyCoachID
        ,AvailableCoachID
        ,ST AS ts
        ,+1 AS EventType
    FROM CTE_Intervals

    UNION ALL

    SELECT
        BusyCoachID
        ,AvailableCoachID
        ,ET AS ts
        ,-1 AS EventType
    FROM CTE_Intervals

    UNION ALL

    SELECT
        CoachID AS BusyCoachID
        ,CoachID AS AvailableCoachID
        ,BusyST AS ts
        ,+1 AS EventType
    FROM #Temp1

    UNION ALL

    SELECT
        CoachID AS BusyCoachID
        ,CoachID AS AvailableCoachID
        ,BusyET AS ts
        ,-1 AS EventType
    FROM #Temp1
)
,C2 AS
(
    SELECT
        C1.*
        ,SUM(EventType)
            OVER (
            PARTITION BY BusyCoachID
            ORDER BY ts, EventType DESC, AvailableCoachID
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        - 1 AS cnt
        ,LEAD(ts) 
            OVER (
            PARTITION BY BusyCoachID 
            ORDER BY ts, EventType DESC, AvailableCoachID) 
        AS NextTS
    FROM C1
)
SELECT
    BusyCoachID AS CoachID
    ,ts AS CanCoachST
    ,NextTS AS CanCoachET
    ,cnt AS NumOfCoaches
FROM C2
WHERE ts <> NextTS
ORDER BY BusyCoachID, CanCoachST
;

最终结果。

+---------+-------------------------+-------------------------+--------------+
| CoachID |       CanCoachST        |       CanCoachET        | NumOfCoaches |
+---------+-------------------------+-------------------------+--------------+
|       1 | 2016-08-17 09:12:00.000 | 2016-08-17 09:24:00.000 |            2 |
|       1 | 2016-08-17 09:24:00.000 | 2016-08-17 09:30:00.000 |            3 |
|       1 | 2016-08-17 09:30:00.000 | 2016-08-17 09:34:00.000 |            1 |
|       1 | 2016-08-17 09:34:00.000 | 2016-08-17 09:55:00.000 |            2 |
|       1 | 2016-08-17 09:55:00.000 | 2016-08-17 10:00:00.000 |            1 |
|       1 | 2016-08-17 10:00:00.000 | 2016-08-17 10:11:00.000 |            2 |
|       3 | 2016-08-17 09:30:00.000 | 2016-08-17 09:34:00.000 |            1 |
|       3 | 2016-08-17 09:34:00.000 | 2016-08-17 09:55:00.000 |            2 |
|       3 | 2016-08-17 09:55:00.000 | 2016-08-17 10:00:00.000 |            1 |
|       4 | 2016-08-17 12:07:00.000 | 2016-08-17 12:27:00.000 |            2 |
|       4 | 2016-08-17 12:27:00.000 | 2016-08-17 13:08:00.000 |            1 |
|       4 | 2016-08-17 13:08:00.000 | 2016-08-17 13:10:00.000 |            0 |
|       6 | 2016-08-17 15:00:00.000 | 2016-08-17 15:10:00.000 |            0 |
|       6 | 2016-08-17 15:10:00.000 | 2016-08-17 15:15:00.000 |            1 |
|       6 | 2016-08-17 15:15:00.000 | 2016-08-17 15:20:00.000 |            2 |
|       6 | 2016-08-17 15:20:00.000 | 2016-08-17 15:25:00.000 |            1 |
|       6 | 2016-08-17 15:25:00.000 | 2016-08-17 15:40:00.000 |            0 |
|       6 | 2016-08-17 15:40:00.000 | 2016-08-17 15:55:00.000 |            1 |
|       6 | 2016-08-17 15:55:00.000 | 2016-08-17 16:00:00.000 |            0 |
|       9 | 2016-08-17 15:00:00.000 | 2016-08-17 15:10:00.000 |            0 |
|       9 | 2016-08-17 15:10:00.000 | 2016-08-17 15:15:00.000 |            1 |
|       9 | 2016-08-17 15:15:00.000 | 2016-08-17 15:20:00.000 |            2 |
|       9 | 2016-08-17 15:20:00.000 | 2016-08-17 15:25:00.000 |            1 |
|       9 | 2016-08-17 15:25:00.000 | 2016-08-17 15:40:00.000 |            0 |
|       9 | 2016-08-17 15:40:00.000 | 2016-08-17 15:55:00.000 |            1 |
|       9 | 2016-08-17 15:55:00.000 | 2016-08-17 16:00:00.000 |            0 |
+---------+-------------------------+-------------------------+--------------+

我建议创建以下索引，以避免执行计划中的一些排序。

CREATE UNIQUE NONCLUSTERED INDEX [IX_CoachID_BusyST] ON #Temp1
(
    CoachID ASC,
    BusyST ASC
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_CoachID_BusyET] ON #Temp1
(
    CoachID ASC,
    BusyET ASC
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_CoachID_AvailableST] ON #Temp2
(
    CoachID ASC,
    AvailableST ASC
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_CoachID_AvailableET] ON #Temp2
(
    CoachID ASC,
    AvailableET ASC
);

然而，在真实数据中，瓶颈可能在其他地方，这可能取决于数据分布情况。该查询相当复杂，如果没有真实数据来调整它，那么就会有太多的猜测。

查询的第一个变体

逐步运行查询，逐个运行CTE，并检查中间结果以了解其工作方式。

CTE_Intervals 提供了与忙碌时间交叉的可用时间段列表。 C1 将开始和结束时间放入同一列，并附带 EventType。这将帮助我们跟踪时间间隔的开始或结束。通过 EventType 的运行总数给出可用教练的数量。 C1 将忙碌的教练合并到混合中，以正确计算没有可用教练的情况。

WITH
CTE_Intervals
AS
(
    SELECT
        TBusy.CoachID AS BusyCoachID
        ,TBusy.BusyST
        ,TBusy.BusyET
        ,CA.CoachID AS AvailableCoachID
        ,CA.AvailableST
        ,CA.AvailableET
        -- max of start time
        ,CASE WHEN CA.AvailableST < TBusy.BusyST
        THEN TBusy.BusyST
        ELSE CA.AvailableST 
        END AS ST
        -- min of end time
        ,CASE WHEN CA.AvailableET > TBusy.BusyET
        THEN TBusy.BusyET
        ELSE CA.AvailableET
        END AS ET
    FROM
        #Temp1 AS TBusy
        CROSS APPLY
        (
            SELECT
                TAvailable.*
            FROM
                #Temp2 AS TAvailable
            WHERE
                -- the same coach can't be available and busy
                TAvailable.CoachID <> TBusy.CoachID
                -- intervals intersect
                AND TAvailable.AvailableST < TBusy.BusyET
                AND TAvailable.AvailableET > TBusy.BusyST
        ) AS CA
)
,C1 AS
(
    SELECT
        BusyCoachID
        ,AvailableCoachID
        ,ST AS ts
        ,+1 AS EventType
    FROM CTE_Intervals

    UNION ALL

    SELECT
        BusyCoachID
        ,AvailableCoachID
        ,ET AS ts
        ,-1 AS EventType
    FROM CTE_Intervals

    UNION ALL

    SELECT
        CoachID AS BusyCoachID
        ,CoachID AS AvailableCoachID
        ,BusyST AS ts
        ,+1 AS EventType
    FROM #Temp1

    UNION ALL

    SELECT
        CoachID AS BusyCoachID
        ,CoachID AS AvailableCoachID
        ,BusyET AS ts
        ,-1 AS EventType
    FROM #Temp1
)
,C2 AS
(
    SELECT
        C1.*
        ,SUM(EventType)
            OVER (
            PARTITION BY BusyCoachID
            ORDER BY ts, EventType DESC, AvailableCoachID
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        - 1 AS cnt
        ,LEAD(ts) 
            OVER (
            PARTITION BY BusyCoachID 
            ORDER BY ts, EventType DESC, AvailableCoachID) 
        AS NextTS
    FROM C1
)
SELECT
    BusyCoachID AS CoachID
    ,ts AS CanCoachST
    ,NextTS AS CanCoachET
    ,cnt AS NumOfCoaches
FROM C2
WHERE ts <> NextTS
ORDER BY BusyCoachID, CanCoachST
;

DROP TABLE #Temp1;
DROP TABLE #Temp2;

结果

我已经为每行代码添加了可用教练的ID注释并进行了计数。

现在我明白了为什么我的初始结果与您期望的结果不同。

+---------+---------------------+---------------------+--------------+
| CoachID |       CanCoachST    |       CanCoachET    | NumOfCoaches |
+---------+---------------------+---------------------+--------------+
|       1 | 2016-08-17 09:12:00 | 2016-08-17 09:24:00 |            2 |  2,5
|       1 | 2016-08-17 09:24:00 | 2016-08-17 09:30:00 |            3 |  2,3,5
|       1 | 2016-08-17 09:30:00 | 2016-08-17 09:34:00 |            2 |  3,5
|       1 | 2016-08-17 09:34:00 | 2016-08-17 09:55:00 |            3 |  3,4,5
|       1 | 2016-08-17 09:55:00 | 2016-08-17 10:11:00 |            2 |  3,4
|       3 | 2016-08-17 09:30:00 | 2016-08-17 09:34:00 |            2 |  1,5
|       3 | 2016-08-17 09:34:00 | 2016-08-17 09:55:00 |            3 |  1,4,5
|       3 | 2016-08-17 09:55:00 | 2016-08-17 10:00:00 |            2 |  1,4
|       4 | 2016-08-17 12:07:00 | 2016-08-17 12:27:00 |            2 |  3,1
|       4 | 2016-08-17 12:27:00 | 2016-08-17 13:08:00 |            1 |  3
|       4 | 2016-08-17 13:08:00 | 2016-08-17 13:10:00 |            0 |  none
|       6 | 2016-08-17 15:00:00 | 2016-08-17 15:10:00 |            0 |  none
|       6 | 2016-08-17 15:10:00 | 2016-08-17 15:15:00 |            1 |  7
|       6 | 2016-08-17 15:15:00 | 2016-08-17 15:20:00 |            2 |  7,8
|       6 | 2016-08-17 15:20:00 | 2016-08-17 15:25:00 |            1 |  8
|       6 | 2016-08-17 15:25:00 | 2016-08-17 15:40:00 |            0 |  none
|       6 | 2016-08-17 15:40:00 | 2016-08-17 15:55:00 |            1 |  7
|       6 | 2016-08-17 15:55:00 | 2016-08-17 16:00:00 |            0 |  none
+---------+---------------------+---------------------+--------------+