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

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

关注
发信
关注(28)粉丝(399)

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

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

问题描述

这是我的门禁桌[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   |         |
+-------+---------+-----------+-------+--------+--------+---------+---------+

OR

只需跳过空的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.





  • 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

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

08-03 23:15
Powered by LMLPHP ©2024 从门禁表中选择先进先出时间 0.039617