从门禁表中选择先进先出时间

Question

这是我的门禁桌[dbo]。[tblAccess]

+------------+--------------+----------+-----------------+------------+
| EmployeeId | EmployeeName | Location |   AccessTime    | ReaderType |
+------------+--------------+----------+-----------------+------------+
| _1346      | A            | L1       | 7/11/2014 10:00 | IN         |
| _1347      | B            | L2       | 7/10/2014 10:58 | IN         |
| _1346      | A            | L3       | 7/11/2014 23:39 | OUT        |
| _1347      | B            | L4       | 7/10/2014 23:58 | OUT        |
| _1364      | C            | L5       | 7/11/2014 10:00 | IN         |
| _1367      | D            | L6       | 7/10/2014 10:58 | IN         |
| _1367      | D            | L7       | 7/10/2014 22:42 | OUT        |
| _1364      | C            | L8       | 7/11/2014 23:58 | OUT        |
| _1422      | E            | L9       | 7/11/2014 23:58 | IN         |
| _1422      | E            | L10      | 7/11/2014 23:10 | IN         |
| _1111      | F            | L20      | 7/10/2014 23:10 | OUT        |
+------------+--------------+----------+-----------------+------------+

情况

• 假设员工将在同一天进出。
• 员工当天将有多个进出，因此需要先进后出。
• 需要每天获取详细信息，两个日期之间，按EmployeeId分组
• 员工的（InTime，InLocation）或（OUTTime，OutLocation）可以为null
• 如果（InTime）为null，则FirstInLocation和duration将为null，并且与OutTime相同（ 结果1中显示 ）。[如果很难，则不要显示IN时间或OUT时间为零的那一天的详细信息（ 在结果2中显示 ）]
• 持续时间=（FirstInTime-LastOutTime）

需要的结果

我想选择EmployeeId，EmployeeName，Date，FirstInTime，FirstInLocation，LastOutTime，LastOutLocation，持续时间。

结果1

+-------+---------+-----------+-------+--------+--------+---------+---------+
| EmpID | EmpName |   Date    | InLoc | InTime | OutLoc | OutTime | Dur(Hr) |
+-------+---------+-----------+-------+--------+--------+---------+---------+
| _1346 | A       | 7/11/2014 | L1    | 10:00  | L3     | 23:39   | 13:39   |
| _1347 | B       | 7/10/2014 | L2    | 10:58  | L4     | 23:58   | 13:02   |
| _1364 | C       | 7/11/2014 | L5    | 10:00  | L8     | 23:58   | 13:58   |
| _1367 | D       | 7/10/2014 | L6    | 10:58  | L7     | 22:42   | 11:44   |
| _1422 | E       | 7/10/2014 | L10   | 23:10  |        |         |         |
| _1111 | F       | 7/10/2014 |       |        | L20    | 23:10   |         |
+-------+---------+-----------+-------+--------+--------+---------+---------+

或者

只需跳过空的IN或OUT行。像这样的东西。

结果2

+-------+---------+-----------+-------+--------+--------+---------+---------+
| EmpID | EmpName |   Date    | InLoc | InTime | OutLoc | OutTime | Dur(Hr) |
+-------+---------+-----------+-------+--------+--------+---------+---------+
| _1346 | A       | 7/11/2014 | L1    | 10:00  | L3     | 23:39   | 13:39   |
| _1347 | B       | 7/10/2014 | L2    | 10:58  | L4     | 23:58   | 13:02   |
| _1364 | C       | 7/11/2014 | L5    | 10:00  | L8     | 23:58   | 13:58   |
| _1367 | D       | 7/10/2014 | L6    | 10:58  | L7     | 22:42   | 11:44   |
+-------+---------+-----------+-------+--------+--------+---------+---------+

到目前为止，我已经通过以下链接进行了一些查询。但是它的解决方案不完整，而且我是CTE的新手，因此我无法编辑链接中给出的任何查询。

• 如何从SQL Server获取首次登录和最后退出时间？
• SQL查询出入勤
• http://database.ittoolbox.com/groups/technical-functional/sql-l/query-to-pair-user-biometric-attendance-record-in-first-in-last-out-format-5132155

我有这个查询。

    ;with cte as
(select *, rank() over(partition by EmployeeId order by [AccessTime]) rn
 from tblAccess)

select src.EmployeeId, src.EmployeeName, convert(date, src.[AccessTime]) as [AccessTime],
concat(datepart(hour,src.[AccessTime]),':',datepart(minute,src.[AccessTime])) as [TimeIn],
concat(datepart(hour,tgt.[AccessTime]),':',datepart(minute,tgt.[AccessTime])) as [TimeOut],
concat(datediff(minute,src.[AccessTime],tgt.[AccessTime])/60,':',datediff(minute,src.[AccessTime],tgt. [AccessTime])%60) as [Hours]
from cte src
inner join cte tgt on src.EmployeeId = tgt.EmployeeId and src.rn + 1 = tgt.rn and src.rn % 2 = 1

它可以返回结果，但是可以满足所有条件。我不熟悉如何编辑CTE。有什么办法吗？

Answer 1

试试这个：

样本数据

CREATE TABLE tblAccess(
    EmployeeID      VARCHAR(20),
    EmployeeName    VARCHAR(20),
    Location        VARCHAR(20),
    AccessTime      DATETIME,
    ReaderType      VARCHAR(3)
)
INSERT INTO tblAccess VALUES
('_1346', 'A', 'L1', '7/11/2014 10:00', 'IN'),
('_1347', 'B', 'L2', '7/10/2014 10:58', 'IN'),
('_1346', 'A', 'L3', '7/11/2014 23:39', 'OUT'),
('_1347', 'B', 'L4', '7/10/2014 23:58', 'OUT'),
('_1364', 'C', 'L5', '7/11/2014 10:00', 'IN'),
('_1367', 'D', 'L6', '7/10/2014 10:58', 'IN'),
('_1367', 'D', 'L7', '7/10/2014 22:42', 'OUT'),
('_1364', 'C', 'L8', '7/11/2014 23:58', 'OUT'),
('_1422', 'E', 'L9', '7/11/2014 23:58', 'IN'),
('_1422', 'E', 'L10', '7/11/2014 23:10', 'IN'),
('_1111', 'F', 'L20', '7/10/2014 23:10', 'OUT');

解决方案

;WITH CTE AS(
    SELECT
        EmployeeID,
        EmployeeName,
        AccessDate = CAST(AccessTime AS DATE),
        AccessTime = CAST(AccessTime AS TIME),
        Location,
        ReaderType,
        In_RN = ROW_NUMBER() OVER(PARTITION BY EmployeeId, CAST(AccessTime AS DATE), ReaderType ORDER BY CAST(AccessTime AS TIME) ASC),
        Out_RN = ROW_NUMBER() OVER(PARTITION BY EmployeeId, CAST(AccessTime AS DATE), ReaderType ORDER BY CAST(AccessTime AS TIME) DESC)
    FROM tblAccess
)
SELECT
    EmployeeID,
    EmployeeName,
    [Date] = CONVERT(VARCHAR(10), AccessDate, 101),
    InLoc = ISNULL(MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN Location END), ''),
    InTime= ISNULL(SUBSTRING(CONVERT(VARCHAR(20), MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN AccessTime END)), 1, 5), ''),
    OutLoc = ISNULL(MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN Location END), ''),
    OutTime = ISNULL(SUBSTRING(CONVERT(VARCHAR(20), MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN AccessTime END)), 1, 5), ''),
    Duration =  ISNULL(RIGHT('00' +             
                CONVERT(VARCHAR(2), DATEDIFF(MINUTE, 
                    MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN AccessTime END), 
                    MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN AccessTime END)
                )/60), 2) + ':' +
                RIGHT('00' +CONVERT(VARCHAR(2), DATEDIFF(MINUTE, 
                    MAX(CASE WHEN ReaderType = 'IN' AND In_RN = 1 THEN AccessTime END), 
                    MAX(CASE WHEN ReaderType = 'OUT' AND OUT_RN = 1 THEN AccessTime END)
                )%60), 2)
            ,'')
FROM CTE
GROUP BY EmployeeID, EmployeeName, AccessDate
ORDER BY EmployeeName, AccessDate

结果

EmployeeID           EmployeeName Date       InLoc    InTime OutLoc  OutTime Duration
-------------------- ------------ ---------- -------- ------ ------- ------- --------
_1346                A            07/11/2014 L1       10:00  L3      23:39   13:39
_1347                B            07/10/2014 L2       10:58  L4      23:58   13:00
_1364                C            07/11/2014 L5       10:00  L8      23:58   13:58
_1367                D            07/10/2014 L6       10:58  L7      22:42   11:44
_1422                E            07/11/2014 L10      23:10          
_1111                F            07/10/2014                 L20     23:10