计算考勤时间差 [英] Calculate time difference for attendance

问题描述

  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日期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查询来获取这些结果的帮助。 p>

我目前的代码正在引起很多其他问题，因为它必须每次都在临时表中重新计算。

解决方案

尝试这一个 -

  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'），
（3175，'20131222 13 ：49：54.000'，'I'），
（3175，'20131222 13：49：55.000'，'I'），
（3175，'20131222 15：58：42.000'，'O '），
（3175，'20131223 06：02：58.000'，'I'），
（3175，'20131223 14：00：29.000'，'O'），
 3175，'20131224 05：17：09.000'，'我'），
（3175，'20131224 12：34：25.000'，'O'），
（3175，'20131224 12：34：26.000'，'O'）
 
 SELECT 
 t.UserID 
，[Date] = DATEADD（dd，0，DATEDIFF（dd，0，t.CheckIn））
，CheckIn = CONVERT（VARCHAR（10） ，t.CheckIn，108）
，CheckOut = CONVERT（VARCHAR（10），t.CheckOut，108）
，[Hours] = CAST（DATEDIFF（MINUTE，t.CheckIn，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，0，DATEDIFF（dd，0，t.Checktime））= DATEADD（dd，0，DATEDIFF（dd，0，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 
  

I have a table with the below sample output.

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

I want to build a query to achieve the below results:

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

Notice: 1. The duplicate IN is ignored.Third and fourth lines in the raw data. 2. Minutes are in decimal point to the hour in the hours calculation.

I need help of the tsql query to use to get these results.

My current code is causing lots of other issues - because it has to be recalculated in temporary tables everytime.

解决方案

Try this one -

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'),
    (3175, '20131222 13:49:54.000', 'I'),
    (3175, '20131222 13:49:55.000', 'I'),
    (3175, '20131222 15:58:42.000', 'O'),
    (3175, '20131223 06:02:58.000', 'I'),
    (3175, '20131223 14:00:29.000', 'O'),
    (3175, '20131224 05:17:09.000', 'I'),
    (3175, '20131224 12:34:25.000', 'O'),
    (3175, '20131224 12:34:26.000', 'O')

SELECT 
      t.UserID
    , [Date] = DATEADD(dd, 0, DATEDIFF(dd, 0, t.CheckIn))
    , CheckIn = CONVERT(VARCHAR(10), t.CheckIn, 108)
    , CheckOut = CONVERT(VARCHAR(10), t.CheckOut, 108)
    , [Hours] = CAST(DATEDIFF(MINUTE, t.CheckIn, 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, 0, DATEDIFF(dd, 0, t.Checktime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, t2.Checktime))
            AND t2.CheckStatus = 'O'
        ORDER BY t2.Checktime
    ) r
    WHERE t.CheckStatus = 'I'
) t
WHERE t.RowNum = 1

Output -

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

这篇关于计算考勤时间差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！