我遇到了一个更大问题的障碍.
作为大型查询的一部分,我需要解决“守夜人”问题.
我有一张表,其中包括日程安排班次：
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
作为查询的一部分,我需要确定在给定时间范围内房间内是否至少有一名守望者.
因此,如果我指定范围2009-1-1 06:00到2009-1-1 12:00,结果是真的,因为第1和第2轮合并以涵盖这个时间段 – 事实上任何数量的轮班都可以链接保持警惕.但是如果我检查了2009-2-1 22:00到2009-1-2 10:00,结果是假的,因为第二天早上4点到6点之间有休息.
我想在LINQ中实现它,或者作为sql Server(2005)中的用户定义函数实现它,因为在这两种情况下,这只是必须运行以识别需要注意的元素的较大查询的逻辑的一部分.真实数据集涉及与任何给定时间段相交的大约一百个移位记录,但并不总是覆盖整个范围.
我发现的最接近的是
How to group ranged values using SQL Server
对于数字范围,但它取决于在下一个范围开始之前结束的每个范围.如果我可以构建相同的手表统一视图,只考虑重叠手表,那么检查是否涵盖了特定时间将是微不足道的.统一视图如下所示：
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
注意：通过拉动所有数据并在其上运行一些手动循环来实现整个过程相对容易,但这是当前的系统,并且由于班次的数量和必须的时间范围的数量,它相当慢被检查.
解决方法
这是一种平整日期范围的方法
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
您必须比较每行中的上一个和下一个日期,看看是否
>当前行的开始日期介于上一行的日期范围之间.
>当前行的结束日期介于下一行的日期范围之间.
使用上面的代码,实现UDF就像下面一样简单.
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
