从门禁表中选择先进先出时间 [英] select first in and last out time from door access table

查看：54 发布时间：2020/10/6 7:09:10 sql sql-server common-table-expression

本文介绍了从门禁表中选择先进先出时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

Here is my door access table, [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        |
+------------+--------------+----------+-----------------+------------+

条件

conditions

假设员工将在同一天进出。 / li>
员工在同一天将有多个输入和输出。因此需要先输入后输出。

需要在一天之间明智地获取详细信息对于雇员，两个日期，按EmployeeId分组

（InTime，InLocation）或（OUTTime，OutLocation）可以为空

如果（InTime）为如果为null，则FirstInLocation和duration将为null，并且与OutTime相同（在结果1中显示）。 [如果很难，则不要显示IN时间或OUT时间为空的那一天的详细信息（在结果2中显示）]

持续时间=（FirstInTime-LastOutTime）

Assume Employees will be IN and OUT in same day.
There will be multiple IN and OUT for same day for employees.So need first IN and Last Out.
need to get details day wise,between two dates, group by EmployeeId
(InTime, InLocation) or (OUTTime, OutLocation) can be null for an employee
If (InTime) is null then FirstInLocation and duration will be null and same for OutTime(shown in Result 1). [If it is hard, then just dont show the details for the day for which IN time or OUT time is null(Shown in Result 2)]
Duration = (FirstInTime - LastOutTime)

需要的结果

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

I want to select EmployeeId, EmployeeName,Date, FirstInTime, FirstInLocation, LastOutTime, LastOutLocation, duration.

结果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行。

Just skip the null IN or OUT row. some thing like this.

结果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还是陌生的，所以我无法编辑链接中给出的任何查询。

So far I have done some query with the following links. But its incomplete solution and i am new to CTE, so i cant edit any of the query given in the links.

如何从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

How to get first login and last logout time from sql server?
SQL Query for time In/Out attendance
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。有没有办法做到？

It return results some what ok but not satisfy all conditions. I dont know how to edit the CTE as I am new to it. Is there any way to do this?

推荐答案

尝试一下：

示例数据

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

这篇关于从门禁表中选择先进先出时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

从门禁表中选择先进先出时间 [英] select first in and last out time from door access table

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

从门禁表中选择先进先出时间 [英] select first in and last out time from door access table

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭