Calculate time difference for attendance

问题:

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.

回答1:

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

回答2:

I took Devart's code and improved it. What I do is I use OUTER APPLY to grab the next row for each "IN" status. I then sort out the bad rows in the where clause. If there are two "IN" in a row, then it grabs the later 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  UserID,
        CAST(I.CheckTime AS DATE) AS [Date],
        CONVERT(VARCHAR(10), I.CheckTime, 108) AS CheckIn,
        CONVERT(VARCHAR(10), O.CheckTime, 108) AS CheckOut,
        CAST(DATEDIFF(MINUTE,I.checkTime,O.CheckTime)/60.0 AS DECIMAL(18,2)) [Hours]
FROM @temp I
OUTER APPLY (
                SELECT TOP 1    Checktime,
                                CheckStatus
                FROM @temp t
                WHERE       t.UserID = I.UserID
                        AND t.Checktime > I.Checktime
                ORDER BY t.Checktime
            ) O
WHERE I.CheckStatus = 'I'
AND O.CheckStatus = 'O'

Results:

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