sql - SQL - 获取多个范围之间的时间总和
问题描述
上图代表了我一直试图在 MSSQL 中完成的事情。
这是一个有多个时间段的时间表,A到B是加班,B到C早上正常工作,C到D午餐时间,D到E下午正常工作,E到F也加班。
为了简单起见,我手动将 A、B、C、D、E 和 F 声明为固定值。实际上,这些将来自另一个表,但这与这个问题无关。
垂直箭头表示打卡/打卡时间。
所以,假设 IN 是早上 8 点,OUT 是晚上 7 点。
如何检索以下内容?
工作:8小时
午餐:1小时
加班:2h
例如,不使用混乱的IF并且知道 IN 可能位于 B 和 C 的中间。
上午 11 点打卡,晚上 7 点打卡的人现在工作 = 6 小时,午餐 = 1 小时,加班 = 1 小时。
谢谢你。
举个例子,我当前的意大利面条代码,肯定有一种更优雅(和更干净)的方式来实现这一点。
declare @clockIn time = '08:00'
declare @clockOut time = '19:00'
declare @a time= '00:00'
...all possible limits defined here...
declare @f time= '23:59'
tests using C to D only:
-- in block
IF(@clockIn >= @c
AND @clockOut <= @d)
BEGIN
SET @at = @at + ABS(DATEDIFF(hour, @clockIn, @clockOut));
END;
-- outside block
IF(@clockIn < @c
AND @clockOut > @d)
BEGIN
SET @at = @at + ABS(DATEDIFF(hour, @c, @d));
END;
-- cIn in block, cOut outside
IF(@clockIn >= @c
AND @clockOut > @d)
BEGIN
SET @at = @at + ABS(DATEDIFF(hour, @clockIn, @d));
END;
-- cIn outside, cIn inside
IF(@clockIn < @c
AND @clockOut <= @d)
BEGIN
SET @at = @at + ABS(DATEDIFF(hour, @c, @clockOut));
END
select @ax as overtime,@at as work,@ai as lunch
解决方案
如果不以某种方式构建 if-then 逻辑,我想不出一种方法可以做到这一点,所以这取决于你如何做到这一点。我认为最简单(也许是最易读)的方法是在每次需要的时候只使用一个 case 表达式,也许会留下一些关于它背后的逻辑的评论,这样任何遇到它的人都可以更容易地理解它是如何工作的。
例如,这样的事情应该足够简单而不会太乱(IMO):
DECLARE @Clock TABLE (Clockin TIME NOT NULL, Clockout TIME NOT NULL);
INSERT @Clock VALUES ('12:30', '19:00');
DECLARE @Times TABLE (WorkStart TIME NOT NULL, LunchStart TIME NOT NULL, LunchEnd TIME NOT NULL, WorkEnd TIME NOT NULL);
INSERT @Times VALUES ('09:00', '12:00', '13:00', '18:00');
SELECT
OverTime =
(CASE -- Overtime hours in the morning.
WHEN C.Clockin < T.WorkStart -- Clocked in before official work start.
THEN DATEDIFF(MINUTE, C.Clockin, CASE WHEN C.Clockout > T.WorkStart THEN T.WorkStart ELSE C.Clockout END)
ELSE 0
END +
CASE -- Overtime hours in the evening.
WHEN C.Clockout > T.WorkEnd -- Clocked out after official work end.
THEN DATEDIFF(MINUTE, CASE WHEN C.Clockin > T.WorkEnd THEN C.Clockin ELSE T.WorkEnd END, C.Clockout)
ELSE 0
END) / 60.0,
Work =
(CASE -- Work hours for the morning.
WHEN C.Clockout > T.WorkStart AND C.Clockin < T.LunchStart -- Clocked out after official work start and clocked in before official lunch start.
THEN DATEDIFF(MINUTE, CASE WHEN C.Clockin < T.WorkStart THEN T.WorkStart ELSE C.Clockin END, CASE WHEN C.Clockout < T.LunchStart THEN C.Clockout ELSE T.LunchStart END)
ELSE 0
END +
CASE -- Work hours for the afternoon.
WHEN C.Clockin < T.WorkEnd AND C.Clockout > T.LunchEnd -- Clocked out after official lunch end and clocked in before official work end.
THEN DATEDIFF(MINUTE,CASE WHEN C.Clockin < T.LunchEnd THEN T.LunchEnd ELSE C.Clockin END, CASE WHEN C.Clockout < T.WorkEnd THEN C.Clockout ELSE T.WorkEnd END)
ELSE 0
END) / 60.0,
Lunch =
CASE -- Lunch hours.
WHEN C.Clockin < T.LunchEnd AND C.Clockout > T.LunchStart -- Clocked in before official lunch end and clocked out after official lunch start.
THEN DATEDIFF(MINUTE, CASE WHEN C.Clockin > T.LunchStart THEN C.Clockin ELSE T.LunchStart END, CASE WHEN C.Clockout < T.LunchEnd THEN C.Clockout ELSE T.LunchEnd END)
ELSE 0
END / 60.0
FROM @Clock AS C
CROSS JOIN @Times AS T;
推荐阅读
- amazon-web-services - AmazonMQ 仅限 5 个消费者
- typescript - 在打字稿中,我不明白为什么会收到错误“Service1 | 服务2 | null' 不可分配给类型 '(Service1 & Service2) | 无效的'
- ruby-on-rails - Rails 6 表单不显示错误 - nil:NilClass 的未定义方法“错误”
- python - Pandas 根据另一列的条件重置 cumsum()
- python - 使用python查找两个符号之间的字符而无需重新
- google-apps-script - 从一个 Google 表格单元格中提取多个链接,然后使用 Google Apps 脚本将它们作为超链接粘贴到 Google 文档中
- logic - 如何在形式系统中证明¬(2|9)?
- python - Python3 firebase 存储 SDK 无法上传到模拟器
- python - 去除多列的基线
- ansible - Ansible 使用 lineinfile 包含带有变量问题的单引号