计算夜班持续时间

SQL006 发表于 Dev

SQL006

我正在计算 Night_Start_Time、Night_Shift_End、Night_Shift_Duration

我们已经定义了 Night_Shift_Start_Time = 22:00 hrs 和 Night_Shift_End_Time = 06:00 hrs。

要获得夜班资格，员工应至少工作 30 分钟，否则将不被视为夜班

例如；

03:00 pm - 22:29 pm，在这种情况下，员工只工作 29 分钟，少于 30 分钟，不属于夜班
03:00 pm -- 22:30 pm 是夜班，员工工作了 30 分钟。
05:29 am -- 2:00 pm 因为员工工作了 31 分钟 (06:00 - 05:29) 它带有夜班
05:31 am - 2:00 pm 因为员工工作了 29 分钟夜班时间，所以不能认为是夜班。

表

CREATE TABLE #Shift 
  (Eid int,
   Shift_Start datetime,
   Shift_End datetime);
GO

INSERT INTO #Shift
VALUES
  (1,'20170522 20:00:00.000','20170523 06:00:00.000'),
  (2,'20170522 02:00:00.000','20170522 12:00:00.000'),
  (3,'20170522 23:00:00.000','20170523 08:00:00.000'),
  (4,'20170522 23:00:00.000','20170523 00:00:00.000'),
  (5,'20170522 00:00:00.000','20170522 05:00:00.000'),
  (6,'20170522 15:00:00.000','20170522 21:00:00.000');
GO

预期输出

Eid  Shift_Start              Shift_End                Night_Start_Time         Night_Shift_End          Night_Shift_Duration
1    2017-05-22 20:00:00.000  2017-05-23 06:00:00.000  2017-05-22 22:00:00.000  2017-05-23 06:00:00.000  8
2    2017-05-22 02:00:00.000  2017-05-22 12:00:00.000  2017-05-22 02:00:00.000  2017-05-22 06:00:00.000  4
3    2017-05-22 23:00:00.000  2017-05-23 08:00:00.000  2017-05-22 23:00:00.000  2017-05-23 06:00:00.000  7
4    2017-05-22 23:00:00.000  2017-05-23 00:00:00.000  2017-05-22 23:00:00.000  2017-05-23 00:00:00.000  1
5    2017-05-22 00:00:00.000  2017-05-22 05:00:00.000  2017-05-22 00:00:00.000  2017-05-22 05:00:00.000  5
6    2017-05-22 15:00:00.000  2017-05-22 21:00:00.000  NULL                     NULL                     0

太漂亮

请尝试以下代码...

SELECT Eid,
       Shift_Start,
       Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) >= 1800 THEN
               CASE
                   WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                       CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                   ELSE
                       Shift_Start
               END
           ELSE
               NULL
       END AS Night_Start_Time,
       CASE
           WHEN DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) >= 1800 THEN
               CASE
                   WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                       CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                   ELSE
                       Shift_End
               END
           ELSE
               NULL
       END AS Night_Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) >= 1800 THEN
               DATEDIFF( S,
                          CASE
                              WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                                  CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                              ELSE
                                  Shift_Start
                          END,
                          CASE
                              WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                                  CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                              ELSE
                                  Shift_End
                          END
                        ) / 3600.0
           ELSE
               0.0
       END AS Night_Shift_Duration
FROM #Shift;

该语句首先使用以下部分来选择夜班开始时间（相对于班次开始时间）...

CASE
    WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
        CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
    ELSE
        Shift_Start
END

此部分提取的TIME组件Shift_Start并进行测试以查看它是否在夜班时间范围之外。如果是，则它提取的DATE组件Shift_Start并将其转换为字符串，以便它可以将日期与夜班开始时间的字符串表示连接起来。然后将连接的字符串转换为其等效DATETIME值。

如果的TIME组件Shift_Start不在夜班时间范围之外，则Shift_Start返回的值。

以下语句使用类似的逻辑来确定夜班结束时间...

CASE
    WHEN CAST( Shift_End AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
        CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
    ELSE
        Shift_End
END

如果所识别的夜班结束时间的记录超过1800秒（30表示为秒分钟，从而允许通过所述第二电平的精度）之后所识别的夜班开始时间该记录，则该字段Night_Start_Time和Night_Shift_End该记录将是设置为他们各自的夜班时间，Night_Shift_Duration并设置为他们之间的小时差，计算方法为秒差除以一小时的秒数。

我已经针对使用您提供的脚本创建的数据库测试了我的语句（谢谢您），并取得了预期的结果。

如果您有任何问题或意见，请随时发表相应的评论。

附录1

以下语句是针对示例数据库运行的，以允许进一步测试...

INSERT INTO #Shift
VALUES ( 7,
         '20170522 05:31:00',
         '20170522 22:01:00' ),
       ( 8,
         '20170522 04:31:00',
         '20170522 22:01:00' );

附录二

以下语句是上述语句的变体，它使用子查询来确定每条记录的Night_Start_Time和的值，而不考虑两者之间的差异。主查询使用如此生成的值来确定、和的最终值。Night_Shift_EndNight_Start_TimeNight_Shift_EndNight_Shift_Duration

我不确定哪个更有效。

SELECT Eid,
       Shift_Start,
       Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                        ) >= 1800 THEN
               Night_Start_Time
           ELSE
               NULL
       END AS Night_Start_Time,
       CASE
           WHEN DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                        ) >= 1800 THEN
               Night_Shift_End
           ELSE
               NULL
       END AS Night_Shift_End,
       CASE
           WHEN DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                        ) >= 1800 THEN
               DATEDIFF( S,
                          Night_Start_Time,
                          Night_Shift_End
                       ) / 3600.0
           ELSE
               0.0
       END AS Night_Shift_Duration
FROM ( SELECT Eid,
              Shift_Start,
              Shift_End,
              CASE
                  WHEN CAST( Shift_Start AS TIME ) BETWEEN '05:30:01' AND '22:00:00' THEN
                      CAST( ( CAST( CAST( Shift_Start AS DATE ) AS VARCHAR ) + ' 22:00' ) AS DATETIME )
                  ELSE
                      Shift_Start
              END Night_Start_Time,
              CASE
                  WHEN CAST( Shift_End AS TIME ) BETWEEN '06:00:01' AND '22:29:59' THEN
                      CAST( ( CAST( CAST( Shift_End AS DATE ) AS VARCHAR ) + ' 06:00' ) AS DATETIME )
                  ELSE
                      Shift_End
              END Night_Shift_End
       FROM #Shift
     ) AS shiftTimesFinder;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-06-6

我来说两句

0 条评论

登录后参与评论

TOP 榜单

文章

计算夜班持续时间

计算夜班持续时间

Linux的官方Adobe Flash存储库是否已过时？

用日期数据透视表和日期顺序查询

应用发明者仅从列表中选择一个随机项一次

Java Eclipse中的错误13，如何解决？

在Windows 7中无法删除文件（2）

在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

套接字无法检测到断开连接

带有错误“ where”条件的查询如何返回结果？

有什么解决方案可以将android设备用作Cast Receiver？

Mac OS X更新后的GRUB 2问题

ggplot：对齐多个分面图-所有大小不同的分面

验证REST API参数

如何从视图一次更新多行（ASP.NET - Core）

尝试反复更改屏幕上按钮的位置 - kotlin android studio

计算数据帧中每行的NA

检索角度选择div的当前值

离子动态工具栏背景色

UITableView的项目向下滚动后更改颜色，然后快速备份

VB.net将2条特定行导出到DataGridView

蓝屏死机没有修复解决方案

通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException