SQL 中时间段的交叉和合并 [英] Intersection and consolidation of time periods in SQL

问题描述

我想实现在

然后使用该结果和另一个来自不同表的结果，我想找出两者之间的交集，像这样但只有 2 个输入(找到两者中都存在的句点):

一旦我有了十字路口，就可以总结它的时间.

这里我提供了一个带有预期输出的 SQL Fiddle 示例:

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

解决方案

样本数据准备

CREATE TABLE TableToCombine([IdDoc] int IDENTITY(1,1), [IdEmployee] int, [StartDate] datetime, [EndDate] datetime);INSERT INTO TableToCombine(IdEmployee、开始日期、结束日期)价值观(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');创建表 TableToIntersect([IdDoc] int IDENTITY(1,1), [OrderId] int, [StartDate] datetime, [EndDate] datetime);INSERT INTO TableToIntersect(订单 ID、开始日期、结束日期)价值观(1, '2018-01-01 09:00:00', '2018-01-02 12:00:00');

查询:

 with ExpectedCombineOutput as (选择grp, StartDate = min(StartDate), EndDate = max(EndDate)从 (选择*, sum(iif(cd between StartDate and EndDate, 0, 1))over(order by StartDate) grp从 (选择*, lag(EndDate) over (order by IdDoc) cd从TableToCombine) t) t按 grp 分组)选择a.grp, StartDate = iif(a.StartDate = b.StartDate 上加入 TableToIntersect b

交叉时间间隔在 CTE 中组合.然后加入你的 intersectTable 以找到重叠的时期.如果 a.StartDate <; 两个周期重叠b.EndDate 和 a.EndDate >b.开始日期

I want to achieve similar function that is available in Time Period Library for .NET, but in SQL.

First, I have a table with several rows with an Start Date and an End Date, and I want to consolidate them together like this:

Then with that result and another coming from a different table, I want to find out the intersection between the two of them, like this but only 2 inputs (find the periods that are present in both):

Once I have the intersection is just summing up the time on it.

Here I provide a SQL Fiddle with the expected output with an example:

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

解决方案

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

Intersecting time intervals are combined in CTE. And then joined with your intersectTable to find overlapping periods. Two periods overlap if a.StartDate < b.EndDate and a.EndDate > b.StartDate

这篇关于SQL 中时间段的交叉和合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！