这是一个范围交叉问题:你正在寻找一个数字范围:
4/01/2010 08:30:00 - 04/01/2010 17:30:00
此范围可以表示为数字 - 从一天开始的微秒或秒,例如:
[1000000, 3000000]
并且你想找到它不会与以下任何一个碰撞的部分:
[1200000, 1250000] [1250000, 1490000] [1500000, 1950000] ...
当翻译成数字格式时, 它真的看起来像这个范围交叉算法 ,它几乎可以用任何语言实现。
的 编辑: 强>
有一个关于日期范围的非常有趣的讨论,有很好的插图和解释 这里 。
我建议使用Eric H的方法。有了这个免责声明,这有点令人讨厌,但如果由于某种原因无法访问数字表,它确实提供了做同样事情的方法。我确信它可以改进,我只是觉得没有使用数字表尝试它:
Declare @Start DateTime, @End DateTime Select @Start = '04/01/2010 09:30' , @End = '04/01/2010 17:30' --Table Creation Stuff Declare @y Table (ColleagueId Int, DepartmentId Int, DateIn DateTime, DateOut DateTime) Insert @y Select 1, 1, '04/01/2010 08:45' , '04/01/2010 11:45' Union All Select 2 , 1, '04/01/2010 09:00' , '04/01/2010 12:15' Union All Select 3 , 1, '04/01/2010 10:00' , '04/01/2010 12:00' Union All Select 4 , 1, '04/01/2010 12:30' , '04/01/2010 17:00' Union All Select 1 , 1, '04/01/2010 12:45' , '04/01/2010 17:15' Union All Select 3 , 1, '04/01/2010 13:00' , '04/01/2010 17:25' --------- Select DateDiff(minute, @Start, @End) -- TotalTime - Sum(DateDiff(minute, Case When DateIn < @Start Then @Start Else DateIn End, Case When DateOut > @End Then @End Else DateOut End)) --StaffedTime as UnmannedTime From ( Select Min(din) DateIn, dout DateOut From ( Select Min(y.DateIn) din, Max(y2.DateOut) dout From @y y Inner Join @y y2 on y.DateOut >= y2.DateIn --you probably want to close the other end of these filters, but leave some room --(to handle the guy who started @ 7:45, etc...) Where y.DateIn < @End and y2.DateOut > @Start Group By y.DateIn ) x Group By dout ) q
的 编辑 强> 添加了上述案例陈述,以便在特定时期在@Start之前开始(或在@End之后结束)时处理StaffedTime的计算