sql – 计算出勤率的时差

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

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

（编辑：源码网）

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