SQL 查询显示多个日期范围之间的差距

Question

我正在处理一个 SSRS/SQL 项目并尝试编写一个查询来获取日期之间的差距，我完全不知道如何编写这个。基本上我们有许多可以安排使用的设备，我需要一份报告不使用时显示。

我有一个包含设备 ID、EventStart 和 EventEnd 时间的表，我需要运行查询以获取每个设备的这些事件之间的时间，但我不确定如何执行此操作。

例如:

Device 1 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
Device 1 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`    
Device 1 Event C runs from `02/01/2012 18:00 - 02/01/2012 20:00`    
Device 2 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
Device 2 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`

我的查询结果应该是

`Device 1 01/01/2012 10:00 - 01/01/2012 18:00`
`Device 1 01/01/2012 20:00 - 02/01/2012 18:00`
`Device 2 01/01/2012 10:00 - 01/01/2012 18:00`

此表中平均大约有 4 - 5 个设备，可能有 200 - 300 + 个事件。

更新:

好的，我会更新它以尝试提供更多信息，因为我似乎没有很好地解释这一点(对不起!)

我正在处理的是一个包含事件详细信息的表，每个事件都是飞行模拟器的预订，我们有许多飞行模拟(称为表中的设备)，我们正在尝试生成一个 SSRS 报告，其中我们可以给客户显示每个 SIM 卡可用的天数/时间。

所以我将传入一个开始/结束日期参数并选择这些日期之间的所有可用性。结果应显示为:

Device   Available_From       Available_To
 1       01/01/2012 10:00    01/01/2012 18:00`
 1       01/01/2012 20:00    02/01/2012 18:00`
 2       01/01/2012 10:00    01/01/2012 18:00`

此外，事件有时可能会重叠，尽管这种情况非常罕见，并且由于数据错误，一个设备上的事件与不同设备上的事件重叠并不重要，因为我需要分别了解每个设备的可用性。

Answer 1

查询:

假设包含区间的字段被命名为 Start和 Finish , 表名为 YOUR_TABLE , 查询...

SELECT Finish, Start
FROM
    (
        SELECT DISTINCT Start, ROW_NUMBER() OVER (ORDER BY Start) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Start > T2.Start AND T1.Start < T2.Finish
            )
        ) T1
    JOIN (
        SELECT DISTINCT Finish, ROW_NUMBER() OVER (ORDER BY Finish) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Finish > T2.Start AND T1.Finish < T2.Finish
            )
    ) T2
    ON T1.RN - 1 = T2.RN
WHERE
    Finish < Start

...在您的测试数据上给出以下结果:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

这个查询的重要属性是它可以在 上工作。重叠 间隔也是如此。

算法:

1. 合并重叠区间

子查询 T1仅接受 的间隔开始超出其他区间 .子查询 T2对间隔结束做同样的事情。这就是消除重叠的原因。
DISTINCT如果有两个相同的间隔开始(或结束)都在其他间隔之外，这一点很重要。 WHERE Finish < Start简单地消除任何空间隔(即持续时间 0)。

我们还附加了一个与时间顺序相关的行号，这将在下一步中使用。
T1产量:

Start                       RN
2012-01-01 08:00:00.000     1
2012-01-01 18:00:00.000     2

T2产量:

Finish                      RN
2012-01-01 10:00:00.000     1
2012-01-01 20:00:00.000     2

2. 重构结果

我们现在可以重建“事件”或“非事件”区间。

通过将 的末尾放在一起来重构非事件间隔。上一个 与下一个开始的间隔，因此 - 1在 ON条款。实际上，我们把...

Finish                      RN
2012-01-01 10:00:00.000     1

...和...

Start                       RN
2012-01-01 18:00:00.000     2

...一起，导致:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

(可以通过将 T1 中的行与 T2 中的行放在一起，通过使用 JOIN ... ON T1.RN = T2.RN 并恢复 WHERE 来重建事件区间。)

这个例子:

这是一个稍微现实一点的例子。以下测试数据:

Device      Event      Start                      Finish
Device 1    Event A    2012-01-01 08:00:00.000    2012-01-01 10:00:00.000
Device 2    Event B    2012-01-01 18:00:00.000    2012-01-01 20:00:00.000
Device 3    Event C    2012-01-02 11:00:00.000    2012-01-02 15:00:00.000
Device 4    Event D    2012-01-02 10:00:00.000    2012-01-02 12:00:00.000
Device 5    Event E    2012-01-02 10:00:00.000    2012-01-02 15:00:00.000
Device 6    Event F    2012-01-03 09:00:00.000    2012-01-03 10:00:00.000

给出以下结果:

Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000
2012-01-01 20:00:00.000     2012-01-02 10:00:00.000
2012-01-02 15:00:00.000     2012-01-03 09:00:00.000