检查时间范围的重叠，守望的问题[SQL]

本文介绍了检查时间范围的重叠，守望的问题[SQL]的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我遇到了一个更大的问题路障。

I am running into a road block on a larger problem.

作为一个大型的查询的一部分，我需要解决一个守夜人的问题。 我有一个表与时间表转变为这样的：

As part of a large query I need to solve a "night watchman" problem. I have a table with schedule shifts as such:

ID | Start | End 1 | 2009-1-1 06:00 | 2009-1-1 14:00 2 | 2009-1-1 10:00 | 2009-1-1 18:00 3 | 2009-2-1 20:00 | 2009-2-2 04:00 4 | 2009-2-2 06:00 | 2009-2-2 14:00

作为查询的一部分，我需要确定是否存在至少1值勤人在房间时刻对于给定的时间范围。

As part of a query, I need to determine if there is at least 1 watchman in a room at all times for a given time range.

所以，如果我指定的范围 2009-1-1 06:00 到 2009-1-1 12:00 ，结果是正确的，因为转移1和2的合并覆盖这个时间段 - 事实上任意数量的变化中，可以链接到保持手表了。但是，如果我检查 2009-2-1 22:00 到 2009-1-2 10:00 ，结果是假的，因为有4至早上6点第二天上午休息。

So if I specified the range 2009-1-1 06:00 to 2009-1-1 12:00 , the result is true, because shifts 1 and 2 merge to cover this time period - in fact any number of shifts could be chained to keep the watch up. However if I checked 2009-2-1 22:00 to 2009-1-2 10:00, the result is false because there is a break between 4 and 6am the following morning.

我想实现这个的或者的LINQ中，或在SQL Server中的用户定义函数（2005年），在这两种情况下，这是一个更大的查询的逻辑只是一部分必须运行来标识需要注意的元素。真正的数据集包括大约一百移记录相交任何给定的时间段，但不总是覆盖整个范围

I would like to implement this either in LINQ, or as a user defined function in SQL Server (2005), as in both cases this is just a part of the logic of a larger query that must be run to identify elements that need attention. The real dataset involves about a hundred shift records intersecting any given time period, but not always covering the whole range.

我发现的最接近的是 如何分组不等使用SQL Server 值 对于数字范围，但是这取决于每个范围内结束接下来的范围开始之前。如果我可以构造的手表相同的统一视图，只考虑重叠手表考虑，那么这将是微不足道的检查，如果一个特定的时间范围内。统一的观点是这样的：

The closest I've found is How to group ranged values using SQL Server for number ranges, however it depends on each range ending just before the next range starts. If I could construct the same unified view of the watches, only taking overlapping watches into consideration, then it would be trivial to check if a specific time was covered. A unified view would look like this:

Start | End 2009-1-1 06:00 | 2009-1-1 18:00 2009-2-1 20:00 | 2009-2-2 04:00 2009-2-2 06:00 | 2009-2-2 14:00

请注意：这整个事情会相对容易，只需拉动的所有数据并运行就可以了一些手动循环来实现，但它是与当前的系统，它的相当慢，因为班次的数量和时间的数范围必须进行检查。

Note: This whole thing would be relatively easy to implement by just pulling all the data and running some manual loop on it, however that is the current system, and its rather slow because of the number of shifts and the number of time ranges that must be checked.

推荐答案

下面是一种扁平化的日期范围是这样

Here is a way to flatten date range like this

Start | End 2009-1-1 06:00 | 2009-1-1 18:00 2009-2-1 20:00 | 2009-2-2 04:00 2009-2-2 06:00 | 2009-2-2 14:00

您要比较的 previous 的和的下一步的日期，在每一行，看看是否

You have to compare previous and next dates in each row and see whether

• 在当前行的开始日期落在previous行的日期范围之间。
• 在当前行的结束日期落在下一行的日期范围之间。

• Current row's Start date falls between previous row's date range.
• Current row's End date falls between next row's date range.

使用上述code，实现UDF是为随后的那么简单。

Using above code, implementing UDF is as simple as followed.

create function fnThereIsWatchmenBetween(@from datetime, @to datetime) returns bit as begin declare @_Result bit declare @FlattenedDateRange table ( Start datetime, [End] datetime ) insert @FlattenedDateRange(Start, [End]) select distinct Start = case when Pv.Start is null then Curr.Start when Curr.Start between Pv.Start and Pv.[End] then Pv.Start else Curr.Start end, [End] = case when Curr.[End] between Nx.Start and Nx.[End] then Nx.[End] else Curr.[End] end from shift Curr left join shift Pv on Pv.ID = Curr.ID - 1 --; prev left join shift Nx on Nx.ID = Curr.ID + 1 --; next if exists( select 1 from FlattenedDateRange R where @from between R.Start and R.[End] and @to between R.Start and R.[End]) begin set @_Result = 1 --; There is/are watchman/men during specified date range end else begin set @_Result = 0 --; There is NO watchman end return @_Result end