加入收藏 | 设为首页 | 会员中心 | 我要投稿 上饶站长网 (https://www.0793zz.com.cn/)- 数据库平台、视觉智能、智能搜索、决策智能、迁移!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

sql – 计算出勤率的时差

发布时间:2021-03-30 22:03:31 所属栏目:MsSql教程 来源:网络整理
导读:我有一个表格,下面的示例输出. UserID Checktime CheckStatus3175 2013-12-22 07:02:10.000 I3175 2013-12-22 13:01:01.000 O3175 2013-12-22 13:49:54.000 I3175 2013-12-22 13:49:55.000 I3175 2013-12-22 15:58:42.000 O3175 2013-12-23 06:02:58.000 I317

我有一个表格,下面的示例输出.

UserID  Checktime              CheckStatus
3175    2013-12-22 07:02:10.000     I
3175    2013-12-22 13:01:01.000     O
3175    2013-12-22 13:49:54.000     I
3175    2013-12-22 13:49:55.000     I
3175    2013-12-22 15:58:42.000     O
3175    2013-12-23 06:02:58.000     I
3175    2013-12-23 14:00:29.000     O
3175    2013-12-24 05:17:09.000     I
3175    2013-12-24 12:34:25.000     O
3175    2013-12-24 12:34:26.000     O

我想构建一个查询来实现以下结果:

UserID  Date       CheckIn   CheckOut Hours
3175    2013-12-22 07:02:10  13:01:0  5.98
3175    2013-12-22 13:49:54  15:58:42 2.15

注意:
1.忽略重复的IN.原始数据中的第三和第四行.
2.分钟数小时为小时计算中的小时.

我需要tsql查询的帮助才能获得这些结果.

我当前的代码导致了许多其他问题 – 因为每次都必须在临时表中重新计算.

解决方法

试试这个 –
DECLARE @temp TABLE
(
    UserID INT,Checktime DATETIME,CheckStatus CHAR(1)
)

INSERT INTO @temp (UserID,Checktime,CheckStatus)
VALUES 
    (3175,'20131222 07:02:10.000','I'),(3175,'20131222 13:01:01.000','O'),'20131222 13:49:54.000','20131222 13:49:55.000','20131222 15:58:42.000','20131223 06:02:58.000','20131223 14:00:29.000','20131224 05:17:09.000','20131224 12:34:25.000','20131224 12:34:26.000','O')

SELECT 
      t.UserID,[Date] = DATEADD(dd,DATEDIFF(dd,t.CheckIn)),CheckIn = CONVERT(VARCHAR(10),t.CheckIn,108),CheckOut = CONVERT(VARCHAR(10),t.CheckOut,[Hours] = CAST(DATEDIFF(MINUTE,t.CheckOut) / 60. AS DECIMAL(10,2))
FROM (
    SELECT 
          t.UserID,CheckIn = t.Checktime,CheckOut = r.Checktime,RowNum = ROW_NUMBER() OVER (PARTITION BY t.UserID,r.Checktime ORDER BY 1/0)
    FROM @temp t
    OUTER APPLY (
        SELECT TOP 1 *
        FROM @temp t2
        WHERE t2.UserID = t.UserID
            AND t2.Checktime > t.Checktime
            AND DATEADD(dd,t.Checktime)) = DATEADD(dd,t2.Checktime))
            AND t2.CheckStatus = 'O'
        ORDER BY t2.Checktime
    ) r
    WHERE t.CheckStatus = 'I'
) t
WHERE t.RowNum = 1

输出 –

UserID      Date                    CheckIn    CheckOut   Hours
----------- ----------------------- ---------- ---------- --------
3175        2013-12-22 00:00:00.000 07:02:10   13:01:01   5.98
3175        2013-12-22 00:00:00.000 13:49:54   15:58:42   2.15
3175        2013-12-23 00:00:00.000 06:02:58   14:00:29   7.97
3175        2013-12-24 00:00:00.000 05:17:09   12:34:25   7.28

(编辑:上饶站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读