SQL Server:条件语句逻辑

Question

我想通过减去工作开始时间和工作结束时间内的休息时间来计算实际工作时间。

我有什么：

•Work Start Time (10:10:00)
•Work End Time (14:10:00)
•Break 1 Start Time (10:00:00)
•Break 1 End Time (10:15:00)
•Break 2 Start Time (14:00:00)
•Break 2 End Time (14:30:00)
•Break 3 Start Time (17:45:00)
•Break 3 End Time (18:00:00)

但是有很多条件，例如：

工作开始时间可能在中断 1 开始时间之前

• 开始，然后工作结束时间在中断 1 结束时间之前结束

工作开始时间可能在中断 1 开始时间之前开始

• ，然后工作结束时间在中断 2 开始时间之前结束

工作开始时间可能在中断 2 开始时间

• 之前开始，但在中断 1 结束时间之后开始，然后工作结束时间在中断 3 结束时间之前结束

等等。

如果我写If-Else语句来检查，有很多可能性。

我可以知道有没有更聪明或更简单的方法来检查和计算实际工作时间？

Answer 1

这是我对这个问题的解决方案：

select sum(TimeInCurrentEvent) -- in minutes
from (
select
sum(case when EventType = 'Work Start Time' then 1 
when EventType = 'Work End Time' then -1 end) over (order by TimeOfDay) as InWorkSpan
,sum(case when EventType = 'Break Start Time' then 1 
when EventType = 'Break End Time' then -1 end) over (order by TimeOfDay) as InBreakSpan
,datediff(minute, TimeOfDay, Lead(TimeOfDay, 1, null) over (order by TimeOfDay)) as TimeInCurrentEvent
,*
from WorkEvents
) a
where InWorkSpan = 1 and InBreakSpan = 0

http://sqlfiddle.com/#!18/5f345/12

我在这里使用的最大技巧是两条sum(...) over(...)线。如果当前事件是"工作开始"或位于下一个"工作结束"之前，则第一个将返回 1，否则返回 0。第二个是类似的，只是它跟踪中断。使用这两个值，我们可以在工作开始和结束之间，但在休息开始和结束之外使用末尾的where子句筛选出一组事件。

之后，唯一剩下的就是确定每个事件之间花费的时间。为此，我使用了一个lead(...) over (...)将下一个事件的时间拉到上一行以进行比较。对我们筛选到的行求和将只获得在工作内部和休息时间之外花费的时间。

如果您想更深入地了解此查询的工作原理，我建议仅运行子查询以查看窗口函数返回的内容。

请注意，一些无效数据(连续有两个工作结束时间或以工作结束时间开头(会导致上述总和不返回 0 或 1，并使处理此问题变得更加困难 - 您的问题中没有描述，所以我希望这不是问题！此代码还可以扩展到按用户 ID 分组或通过在over子句中使用partition by语句按日期分组等情况。

Answer 2

如果没有您的示例输入/输出，很难分辨，但基本查询仍然是：

小提琴演示

SELECT CONVERT(varchar(5), DATEADD(minute, 
DATEDIFF(minute, Work Start Time, Work End Time) - 
(
DATEDIFF(minute, Break 1 Start Time, Break 1 End Time) + 
DATEDIFF(minute, Break 2 Start Time, Break 2 End Time) + 
DATEDIFF(minute, Break 3 Start Time, Break 3 End Time)
), 0), 114) 

/* Results: 03:00 */

有条件地更新

SELECT CASE WHEN Break 1 Start Time > Work Start Time THEN
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, Work Start Time, Work End Time) - (DATEDIFF(minute, Break 1 Start Time, Break 1 End Time) + DATEDIFF(minute, Break 2 Start Time, Break 2 End Time) +               DATEDIFF(minute, Break 3 Start Time, Break 3 End Time)), 0), 114) 
WHEN  Break 2 Start Time > Work Start Time AND Break 1 END Time > Work Start Time AND  THEN 
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, Work Start Time, Work End Time) - (DATEDIFF(minute, Break 2 Start Time, Break 2 End Time) +               DATEDIFF(minute, Break 3 Start Time, Break 3 End Time)), 0), 114) 
WHEN  Break 3 Start Time > Work Start Time AND Break 2 END Time > Work Start Time AND  THEN 
CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, Work Start Time, Work End Time) - (DATEDIFF(minute, Break 3 Start Time, Break 3 End Time)), 0), 114) 
ELSE '00:00' END AS [Working Hours]    

/* Results: 03:00 */