SQL中时间段的相交和合并

Question

我想实现 .NET的时间段库中
提供的类似功能，但要实现SQL中的类似功能。

首先，我有一个表，其中有几行带有开始日期和结束日期，
我想像这样将它们合并在一起：

组合

然后，根据该结果和另一个来自不同表的结果，我想
找出它们两者之间的交集，像这样，但是只有2个输入
（找到两个中都存在的周期）：

路口

一旦我有了交叉路口，便只是在上面总结时间。

在这里，我通过示例提供了一个SQL Fiddle及其预期的输出：

http://sqlfiddle.com/#!18/504fa/3

Answer 1

Sample data preparation

CREATE TABLE TableToCombine
    ([IdDoc] int IDENTITY(1,1), [IdEmployee] int, [StartDate] datetime, [EndDate] datetime)
;

INSERT INTO TableToCombine
    (IdEmployee, StartDate, EndDate)
VALUES
    (1, '2018-01-01 06:00:00', '2018-01-01 14:00:00'),
    (2, '2018-01-01 11:00:00', '2018-01-01 19:00:00'),
    (3, '2018-01-01 20:00:00', '2018-01-02 03:00:00'),
    (1, '2018-01-02 06:00:00', '2018-01-02 14:00:00'),
    (2, '2018-01-02 11:00:00', '2018-01-02 19:00:00')
;

CREATE TABLE TableToIntersect
    ([IdDoc] int IDENTITY(1,1), [OrderId] int, [StartDate] datetime, [EndDate] datetime)
;

INSERT INTO TableToIntersect
    (OrderId, StartDate, EndDate)
VALUES
    (1, '2018-01-01 09:00:00', '2018-01-02 12:00:00')
;

Query:

with ExpectedCombineOutput as (
    select
        grp, StartDate = min(StartDate), EndDate = max(EndDate)
    from (
        select
            *, sum(iif(cd between StartDate and EndDate, 0, 1))over(order by StartDate) grp
        from (
            select
                *, lag(EndDate) over (order by IdDoc) cd
            from
                TableToCombine
        ) t
    ) t
    group by grp
)

select 
    a.grp, StartDate = iif(a.StartDate < b.StartDate, b.StartDate, a.StartDate)
    , EndDate = iif(a.EndDate < b.EndDate, a.EndDate, b.EndDate)
from
    ExpectedCombineOutput a
    join TableToIntersect b on a.StartDate <= b.EndDate and a.EndDate >= b.StartDate

相交的时间间隔在CTE中合并。然后与您的intersectTable一起查找重叠的句点。如果两个周期重叠
a.StartDate < b.EndDate and a.EndDate > b.StartDate