出勤时间的 SQL 查询 [英] SQL Query for time In/Out attendance

问题描述

我有一个包含以下示例输出的表格.

I have a table with the below sample output.

    ID_Emp| Name    |   Date
   ----------------------------------
    11    |Jonh     |14/05/2014 8:16
    11    |Jonh     |14/05/2014 13:35
    11    |Jonh     |14/05/2014 17:23
    11    |Jonh     |14/05/2014 21:09
    12    |Elizabe  |14/05/2014 14:06
    12    |Elizabe  |14/05/2014 22:39
    13    |Jimmy    |14/05/2014 8:00
    13    |Jimmy    |14/05/2014 17:12

我想构建一个查询来实现以下结果:

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

    ID_Emp|Name  |Date          |TimeIn |TimeOut|Hours
   -------------------------------------------------------
    11  |Jonh    |14/05/2014    |8:16   |13:35  |5:19
    11  |Jonh    |14/05/2014    |17:23  |21:09  |3:46
    12  |Elizabe |14/05/2014    |14:06  |22:39  |8:33
    13  |Jimmy   |14/05/2014    |8:00   |17:12  |9:12

推荐答案

试试这个:

;with cte as
(select *, rank() over(partition by ID_Emp order by [Date]) rn
 from attendance)

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

警告:我仅在 SQL Server 2008 R2 上对此进行了测试，但我认为经过适当修改后它也可以在 Oracle 上运行.

Caveat: I have tested this on SQL Server 2008 R2 only, but I assume it should work on Oracle as well with appropriate modifications.

说明:我们使用RANK 函数按每个ID_Emp 的日期和时间进行排序.然后，我们加入 ID 并获得行对.最后，为了确保我们不会选择每对连续的行，我们要求源行的排名应该是奇数.

Explanation: We use the RANK function to order by the date and time for each ID_Emp. Then, we join on ID and get pairs of rows. Finally, in order to ensure that we do not select every pair of consecutive rows, we make it a requirement that the source row's rank should be odd.

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