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

Im working on a SSRS / SQL project and trying to write a query to get the gaps between dates and I am completely lost with how to write this.Basically we have a number of devices which can be scheduled for use and I need a report to show when they are not in use.

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

I have a table with Device ID, EventStart and EventEnd times, I need to run a query to get the times between these events for each device but I am not really sure how to do this.

我有一个包含设备ID、EventStart和EventEnd times的表，我需要运行一个查询来获取每个设备的事件之间的时间，但我并不确定如何做到这一点。

For example:

例如:

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`

My query should have as its result

我的查询应该有它的结果

`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`

There will be around 4 - 5 devices on average in this table, and maybe 200 - 300 + events.

在这个表格中平均有4 - 5个设备，可能有200 - 300个以上的事件。

Updates:

更新:

Ok I'll update this to try give a bit more info since I dont seem to have explained this too well (sorry!)

好吧，我将更新这个，尝试给出更多的信息，因为我似乎没有很好地解释这个(抱歉!)

What I am dealing with is a table which has details for Events, Each event is a booking of a flight simulator, We have a number of flight sims( refered to as devices in the table) and we are trying to generate a SSRS report which we can give to a customer to show the days / times each sim is available.

我面对的是一个表细节事件,每个事件是一个飞行模拟器的预订,我们有许多飞行sims(提到设备表中),我们正试图生成SSRS的报告我们可以给客户展示每个sim天/次。

So I am going to pass in a start / end date parameter and select all availabilities between those dates. The results should then display as something like:

因此，我将传入一个开始/结束日期参数，并在这些日期之间选择所有可用性。结果应该如下所示:

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`

Also Events can sometimes overlap though this is very rare and due to bad data, it doesnt matter about an event on one device overlapping an event on a different device as I need to know availability for each device seperately.

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

6 个解决方案

#1

The Query:

Assuming the fields containing the interval are named Start and Finish, and the table is named YOUR_TABLE, the query...

假设包含间隔的字段被命名为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

...gives the following result on your test data:

…给出了测试数据的以下结果:

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

The important property of this query is that it would work on overlapping intervals as well.

这个查询的重要属性是它也可以处理重叠的间隔。

---

The Algorithm:

1. Merge Overlapping Intervals

The subquery T1 accepts only those interval starts that are outside other intervals. The subquery T2 does the same for interval ends. This is what removes overlaps.

子查询T1只接受其他间隔之外的那些间隔开始。子查询T2对间隔结束执行相同的操作。这就是消除重叠的地方。

The DISTINCT is important in case there are two identical interval starts (or ends) that are both outside other intervals. The WHERE Finish < Start simply eliminates any empty intervals (i.e. duration 0).

当两个相同的间隔开始(或结束)都在其他间隔之外时，这种区别是很重要的。在WHERE Finish < Start简单地消除了任何空间隔(即持续时间0)。

We also attach a row number relative to temporal ordering, which will be needed in the next step.

我们还附加一个相对于时间排序的行号，这将在下一步中需要。

The T1 yields:

T1收益率:

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

The T2 yields:

T2收益率:

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

2. Reconstruct the Result

We can now reconstruct either the "active" or the "inactive" intervals.

我们现在可以重建“活动的”或“不活动的”间隔。

The inactive intervals are reconstructed by putting together end of the previous interval with the beginning of the next one, hence - 1 in the ON clause. Effectively, we put...

非活动间隔通过将前一个间隔的结束与下一个间隔的开始组合在一起来重构，因此在ON子句中为- 1。有效地,我们把…

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

...and...

…和…

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

...together, resulting in:

…在一起,导致:

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

(The active intervals could be reconstructed by putting rows from T1 alongside rows from T2, by using JOIN ... ON T1.RN = T2.RN and reverting WHERE.)

(通过将T1中的行与T2中的行放在一起，通过使用JOIN，可以重构活动间隔……)在T1。RN = T2。RN和恢复)。

---

The Example:

Here is a slightly more realistic example. The following test data:

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

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

Gives the following result:

给出了以下结果:

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

#2

First Answer -- but see below for final one with additional constraints added by OP.

第一个答案——但见下面的最后一个附加约束。

-- If you want to get the next startTime after the most recent endTime and avoid overlaps, you want something like:

——如果你想在最近的结束时间之后获得下一个开始时间，并避免重叠，你需要以下内容:

select
    distinct
    e1.deviceId,
    e1.EventEnd,
    e3.EventStart
from Events e1 
join Events e3 on e1.eventEnd < e3.eventStart     /* Finds the next start Time */
and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd)
and not exists (select *                          /* Eliminates an e1 rows if it is overlapped */
                from Events e5 
                where e5.eventStart < e1.eventEnd
                    and e5.eventEnd > e1.eventEnd)

For the case of your three rows:

对于你的三行:

INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')

This gives 1 result:

这给1的结果:

January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800

However, I assume you probably want to match on DeviceId also. In which case, on the joins, you'd add e1.DeviceId = e3.DeviceId and e1.deviceId = e5.deviceId

不过，我想你可能也想在DeviceId上进行匹配。在这种情况下，在连接上添加e1。的DeviceId = e3。DeviceId和e1.deviceId = e5.deviceId

SQL Fiddle here: http://sqlfiddle.com/#!3/3899c/8

SQL小提琴:http://sqlfiddle.com/ ! 3/3899c / 8

--

- - -

OK, final edit. Here's a query adding in deviceIds and adding in a distinct to account for simultenously ending events:

好吧,最后的编辑。这里有一个在deviceIds中添加的查询，并添加了一个不同的查询，以解释同时结束的事件:

SELECT distinct
    e1.DeviceID,
    e1.EventEnd as LastEndTime,
    e3.EventStart as NextStartTime
FROM Events e1 
join Events e3 on e1.eventEnd < e3.eventStart
     and e3.deviceId = e1.deviceId
     and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd
                    and e5.deviceId = e3.deviceId)
where not exists (select * from Events e7 
                    where e7.eventStart < e1.eventEnd
                      and e7.eventEnd > e1.eventEnd
                      and e7.deviceId = e1.deviceId)
order by e1.deviceId, e1.eventEnd

The join to the e3 finds the next start. The join to e5 guarantees that this is the earliest starttime after the current endtime. The join to e7 eliminates a row if the end-time of the considered row is overlapped by a different row.

e3的连接找到下一个开始。e5的连接保证这是当前结束时间之后的最早开始时间。如果考虑的行的结束时间被不同的行重叠，则e7的连接将消除一个行。

For this data:

这个数据:

INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')
insert into Events values (2, '01/02/2012 11:00', '01/02/2012 15:00')
insert into Events values (1, '01/02/2012 10:00', '01/02/2012 12:00')
insert into Events values (2, '01/02/2012 10:00', '01/02/2012 15:00')
insert into Events values (2, '01/03/2012 09:00', '01/03/2012 10:00')

You get this result:

你得到这个结果:

1   January, 01 2012 10:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800
2   January, 01 2012 20:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 02 2012 15:00:00-0800  January, 03 2012 09:00:00-0800

SQL Fiddle here: http://sqlfiddle.com/#!3/db0fa/3

SQL小提琴:http://sqlfiddle.com/ ! 3 / db0fa / 3

#3

Does this solve your issue:

这能解决你的问题吗?

• http://www.simple-talk.com/sql/t-sql-programming/find-missing-date-ranges-in-sql/
• http://www.simple-talk.com/sql/t-sql-programming/find-missing-date-ranges-in-sql/
• http://www.simple-talk.com/sql/t-sql-programming/missing-date-ranges--the-sequel/
• http://www.simple-talk.com/sql/t-sql-programming/missing-date-ranges--the-sequel/

The second one seems more relevant

第二个似乎更相关

'There is a table, where two of the columns are DateFrom and DateTo. Both columns contain date and time values. How does one find the missing date ranges or, in other words, all the date ranges that are not covered by any of the entries in the table'.

有一个表格，其中两列是DateFrom和DateTo。两列都包含日期和时间值。如何找到丢失的日期范围，或者换句话说，所有表中任何一个条目都没有覆盖的日期范围?

#4

I'm going to assume that it's not really this simple... but here's a query based on my current understanding of your scenario:

我假设它不是这么简单……但根据我目前对你的情况的理解，这里有一个问题:

DECLARE @Events TABLE (
    DeviceID INT,
    EventStart DATETIME,
    EventEnd DATETIME
)

INSERT INTO @Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO @Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')

SELECT
    e1.DeviceID,
    e1.EventEnd,
    e2.EventStart
FROM 
    @Events e1 
    JOIN @Events e2 
        ON e2.EventStart = (
            SELECT MIN(EventStart)
            FROM @Events
            WHERE EventStart > e1.EventEnd
        )

#5

I don't really understand your question:

我不太明白你的问题:

"I have a table with Device ID, EventStart and EventEnd times"

"我有一个包含设备ID, EventStart和EventEnd times的表格"

followed by

紧随其后的是

"I need to run a query to get the times between these events"

"我需要运行一个查询来获取这些事件之间的时间"

"For example:

”例如:

Device 1 Event A runs from01/01/2012 08:00 - 01/01/2012 10:00"

设备1事件A运行时间:01/01/2012 08:00 - 01/01/2012 10:00

In order to get this you only need to do

为了得到这个你只需要做

select Device_ID,EventStart,EventEnd from Device 

If you want to get the time difference between start and end dates for each device you can use DATEDIFF:

如果您想获得每个设备的开始日期和结束日期之间的时间差，您可以使用DATEDIFF:

select Device_ID,DATEDIFF(EventEnd - EventStart) from Device

#6

Here is a Postgres solution that I just did, that does not involve stored procedures:

这是我刚才做的一个Postgres解决方案，它不涉及存储过程:

SELECT minute, sum(case when dp.id is null then 0 else 1 end) as s 
FROM generate_series( 
   '2017-12-28'::timestamp,
   '2017-12-30'::timestamp,
   '1 minute'::interval
) minute 
left outer join device_periods as dp
on minute >= dp.start_date and minute < dp.end_date 
group by minute order by minute

The generate_series function generates a table that has one row for each minute in the date range. You can change the interval to 1 second, to be more precise. It is a postgres specific function, but probably something similar exists in other engines.

generate_series函数生成一个表，该表在日期范围内每分钟有一行。你可以把间隔改成1秒，更精确些。它是一个postgres特定函数，但可能在其他引擎中也存在类似的功能。

This query will give you all the minutes that are filled, and all that are blank. You can wrap this query in an outer query, that can group by hours, days or do some window function operations to get the exact output as you need it. For my purposes, I only needed to count if there are blanks or not.

这个查询将为您提供所有已填写的分钟，以及所有空白。您可以将该查询封装到一个外部查询中，该查询可以按小时、天数进行分组，也可以执行一些窗口函数操作，以便根据需要获得准确的输出。就我的目的而言，我只需要数是否有空格。