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

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