文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >计算正常和夜班的工作时间

问计算正常和夜班的工作时间
EN

Stack Overflow用户
提问于 2020-01-10 06:58:47
回答 2查看 196关注 0票数 2

我正在查询以获取员工的工作记录。我的问题是夜班。我知道我需要减去"ShiftStartMinutesFromMidnight“，但是我找不到正确的逻辑。

注意：我不能更改数据库，我只能使用数据库中的数据。

假设我有这些记录。

代码语言：javascript
复制
+----+--------------------------+----------+
| ID |        EventTime         | ReaderNo |
-----+--------------------------+----------+
|  1 |  2019-12-04 11:28:46.000 |    In    |
|  1 |  2019-12-04 12:36:17.000 |    Out   |
|  1 |  2019-12-04 12:39:23.000 |    In    |
|  1 |  2019-12-04 12:51:21.000 |    Out   |
|  1 |  2019-12-05 07:37:49.000 |    In    |
|  1 |  2019-12-05 08:01:22.000 |    Out   |
|  2 |  2019-12-04 22:11:46.000 |    In    |
|  2 |  2019-12-04 23:06:17.000 |    Out   |
|  2 |  2019-12-04 23:34:23.000 |    In    |
|  2 |  2019-12-05 01:32:21.000 |    Out   |
|  2 |  2019-12-05 01:38:49.000 |    In    |
|  2 |  2019-12-05 06:32:22.000 |    Out   |
-----+--------------------------+----------+
代码语言：javascript
复制
WITH CT AS (SELECT
            EIn.PSNID, EIn.PSNNAME
                ,CAST(DATEADD(minute, -0, EIn.EventTime) AS date) AS dt
                ,EIn.EventTime AS LogIn
                ,CA_Out.EventTime AS LogOut
                ,DATEDIFF(minute, EIn.EventTime, CA_Out.EventTime) AS WorkingMinutes
            FROM
                VIEW_EVENT_EMPLOYEE AS EIn
                CROSS APPLY
            (
                SELECT TOP(1) EOut.EventTime
                    FROM VIEW_EVENT_EMPLOYEE AS EOut
                    WHERE
                        EOut.PSNID = EIn.PSNID
                        AND EOut.ReaderNo = 'Out'
                        AND EOut.EventTime >= EIn.EventTime
                    ORDER BY EOut.EventTime
                ) AS CA_Out
            WHERE
                 EIn.ReaderNo = 'In'    
                )
            SELECT
                PSNID
                ,PSNNAME
                ,dt
                ,LogIn
                ,LogOut
                ,WorkingMinutes
            FROM CT
            WHERE dt BETWEEN '2019-11-29' AND '2019-12-05'
            ORDER BY LogIn 
            ;

来自查询的输出

代码语言：javascript
复制
+----+------------+-------------------------+-------------------------+----------------+
| ID |    date    |          In             |         Out             | WorkingMinutes |
-----+------------+-------------------------+-------------------------+----------------+
|  1 | 2019-12-04 | 2019-12-04 11:28:46.000 | 2019-12-04 12:36:17.000 |             68 |
|  1 | 2019-12-04 | 2019-12-04 12:39:23.000 | 2019-12-04 12:51:21.000 |             12 |
|  1 | 2019-12-05 | 2019-12-05 07:37:49.000 | 2019-12-05 08:01:22.000 |             24 |
-----+------------+-------------------------+-------------------------+----------------+

我在想这样的事。当Out在06:25-6:40之间时。但我还需要检查员工，上一天的In在21:50-22:30之间。我需要第二个条件，因为一些第一班的员工可能可以Out，例如6:30。*(1310年是ShiftStartMinutesFromMidnight

查询的第3行

代码语言：javascript
复制
CAST(DATEADD(minute, -0, EIn.EventTime) AS date) AS dt

用以下代码更新第3行.

代码语言：javascript
复制
CASE 
WHEN CAST(CA_Out.LogDate AS time) BETWEEN '06:25:00' AND '06:40:00' 
AND CAST(EIn.LogDate AS time) BETWEEN '21:50:00' AND '22:30:00' THEN CAST(DATEADD(minute, -1310, EIn.LogDate) AS date) 
ELSE CAST(DATEADD(minute, -0, EIn.LogDate) AS date) 
END as dt

预期输出

代码语言：javascript
复制
+----+------------+-------------------------+-------------------------+----------------+
| ID |     date   |          In             |         Out             | WorkingMinutes |
-----+------------+-------------------------+-------------------------+----------------+
|  2 | 2019-12-04 | 2019-12-04 22:11:46.000 | 2019-12-04 23:06:17.000 |             55 |
|  2 | 2019-12-04 | 2019-12-04 23:34:23.000 | 2019-12-05 01:32:21.000 |            118 |
|  2 | 2019-12-04 | 2019-12-05 01:38:49.000 | 2019-12-05 06:32:22.000 |            294 |
-----+------------+-------------------------+-------------------------+----------------+
sql
sql-server
EN

回答 2

Stack Overflow用户

发布于 2020-01-10 08:44:10

假设每个单独日期的总分钟足够：

代码语言：javascript
复制
WITH 
/* enumerate pairs */
cte1 AS ( SELECT *, 
                 COUNT(CASE WHEN ReaderNo = 'In' THEN 1 END) 
                     OVER (PARTITION BY ID 
                           ORDER BY EventTime) pair
          FROM test ),
/* divide by pairs */
cte2 AS ( SELECT ID, MIN(EventTime) starttime, MAX(EventTime) endtime
          FROM cte1
          GROUP BY ID, pair ),
/* get dates range */
cte3 AS ( SELECT CAST(MIN(EventTime) AS DATE) minDate,
                 CAST(MAX(EventTime) AS DATE) maxDate
          FROM test),
/* generate dates list */
cte4 AS ( SELECT minDate theDate
          FROM cte3
          UNION ALL
          SELECT DATEADD(dd, 1, theDate)
          FROM cte3, cte4
          WHERE theDate < maxDate ),
/* add overlapped dates to pairs */
cte5 AS ( SELECT ID, starttime, endtime, theDate
          FROM cte2, cte4 
          WHERE theDate BETWEEN CAST(starttime AS DATE) AND  CAST(endtime AS DATE) ),
/* adjust borders */
cte6 AS ( SELECT ID, 
                 CASE WHEN starttime < theDate
                      THEN theDate
                      ELSE starttime
                      END starttime,
                 CASE WHEN CAST(endtime AS DATE) > theDate
                      THEN DATEADD(dd, 1, theDate)
                      ELSE endtime
                      END endtime,
                 theDate
          FROM cte5 )
/* calculate total minutes per date */
SELECT ID,
       theDate,
       SUM(DATEDIFF(mi, starttime, endtime)) workingminutes
FROM cte6
GROUP BY ID, 
         theDate
ORDER BY 1,2

小提琴

该解决方案是专门制定的详细，一步一步，使您可以轻松理解的逻辑。

您可以自由地将一些CTE合并成一个。如果强烈需要输出(如图所示)，还可以使用前一次cte5和cte2。

该解决方案假定源数据中没有丢失任何记录(每个“in”与“Out”和“向后”强匹配，也没有相邻或重叠的对)。

票数 0
EN

Stack Overflow用户

发布于 2020-01-16 13:49:27

我不知道你在哪里停下来，但我是这样做的，

夜班20:00-05:00一天内00:00 - 5:00；22:00 - 24:00

日班5:00-22:00

为了更容易地进行重叠检查，您需要将所有日期更改为unix时间戳。所以你不必像上面所示的那样分割时间间隔

因此，为提取期间date_from和date_till生成每个周期工作的映射，确保添加假日和假日前异常，其中的周期不同，比如：

Unix值仅用于理解。

代码语言：javascript
复制
unix_from_tim, unix_till_tim, shift_type
1580680800, 1580680800, 1  => example 02-02-2020:22:00:00, 03-02-2020:05:00:00, 1
1580680800, 1580680800, 0 => example 03-02-2020:05:00:00, 03-02-2020:22:00:00, 0
1580680800, 1580680800, 1 => example 03-02-2020:22:00:00, 04-02-2020:05:00:00, 1

..。确保不要计算周期开始/结束时的重叠分钟。

使用unix_from_tim，unix_from_tim，有一个工人行

代码语言：javascript
复制
1580680800, 1580680800=> something like 02-02-2020:16:30:00, 03-02-2020:07:10:00

当您检查重叠时，您可以得到如下所示的ms：

最小(work_period:till，worker_period:till) -最大(work_period:from，worker_period:from)；

简单数字示例：

work_period 3-7

worker_period 5- 12

最小(7，12)-最大值(3，5)=7-5=2//重叠

work_period 3-7

worker_period 8- 12

最小(7，12)-最大值(3，8)=7-8= -1 /如果负不重叠！

work_period 3- 13

worker_period 8- 12

最小( 13，12)-最大(3，8)=13-8=5//完全重叠！

您必须检查每个工人期间的所有重叠时间产生的工作间隔。可能有人可以选择不需要生成work_shift重叠的地方，但如果增加更多的假期、转移的天数、减少的时间天数等，这并不是一项容易的任务。

希望它能帮上忙

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/59676818

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档