sql - 列出员工使用自我加入的工作分钟数
问题描述
我有一个只有 6 列的 SQL Server 表emp_swipe_rec 。我想知道员工 A 和 B 今天(过去 24 小时)工作了多少时间(以分钟为单位)。员工 A 已经刷了一天。员工 B 仍在工作。我们不会为滑入/滑出存储两个不同的列。我们使用 swipe_type (I - In/ O- Out) 来确定员工是否仍在工作或退出。
我需要查询的两个输出。过去 24 小时内刷卡的每位员工的总工作时间。(过去 24 小时内都刷入/刷出员工)现在正在工作的员工列表以及他们刷入之前的总工作时间。(过去 24 小时内只刷入而不刷出)
员工ID | 员工姓名 | Swipe_Type | Swipe_In_Out_Time | 部门 | Emp_Swipe_Rec_PK |
---|---|---|---|---|---|
1 | 一个 | 我 | 2021-03-07 08:00:00.000 | 营销 | 1 |
2 | 乙 | 我 | 2021-03-07 08:00:00.000 | 销售量 | 2 |
1 | 一个 | ○ | 2021-03-07 15:00:00.000 | 营销 | 3 |
我尝试了以下查询,但它说:
DATEDIFF 行中的 starttime 和 endtime 参数的列名无效。
我不知道我做错了什么。
SELECT one.EMPLOYEE_ID,one.EMPLOYEE_NAME
one.SWIPE_IN_OUT_TIME AS starttime,
two.SWIPE_IN_OUT_TIME AS endtime,
DATEDIFF(minute,starttime,endtime) AS diff
FROM emp_swipe_rec one
INNER JOIN emp_swipe_rec two ON two.SWIPE_TYPE <> one.SWIPE_TYPE
WHERE one.Employee_Id = two.Employee_Id AND one.Department = two.Department
解决方案
我认为这满足了您的第一个要求“过去 24 小时内刷卡的每个员工的总工作时间”
这将返回员工 1 的一条记录,因为这是唯一退出的员工(2021 年 8 月!?)
由于这使用窗口函数,您需要使用 SQL Server 2012 或更高版本,您已标记 2008 和 2012
;WITH SwipeIns AS
(
SELECT Employee_ID,
Employee_Name,
Swipe_In_Out_Time
FROM emp_swipe_rec
WHERE Swipe_Type = 'I' AND
Swipe_In_Out_Time > DATEADD(HOUR,-24,GETDATE()) AND
Swipe_In_Out_Time < GETDATE()
),
SwipeOuts AS
(
SELECT Employee_ID,
Swipe_In_Out_Time
FROM emp_swipe_rec
WHERE Swipe_Type = 'O' AND
Employee_ID IN (SELECT Employee_ID FROM SwipeIns) AND
Swipe_In_Out_Time > DATEADD(HOUR,-24,GETDATE())
)
SELECT Employee_ID,
Employee_Name,
SwipeIn,
SwipeOut,
DATEDIFF(MINUTE,SwipeIn,SwipeOut) AS Duration
FROM (
SELECT i.Employee_ID,
i.Employee_Name,
i.Swipe_In_Out_Time AS SwipeIn,
o.Swipe_In_Out_Time AS SwipeOut,
ROW_NUMBER() OVER (PARTITION BY i.Employee_ID ORDER BY o.Swipe_In_Out_Time) AS RowN
FROM SwipeIns i
JOIN SwipeOuts o
ON i.Employee_ID = o.Employee_ID
) a
WHERE RowN = 1
另外,为什么你的表没有标准化?你没有员工表吗?EmployeeName 和 Department 是多余的
推荐阅读
- sql - SQL - 使用子查询提取最近日期的记录时查询超时
- mysql - MySQL | 如何使用正则表达式显示列?
- r - ggplot2 中不同行的两个不同 scale_colour_gradient2()
- xml - python解析xml并获取root下每个子元素的属性值
- postgresql - POSTGRESQL:如何将表的最后一个 id 设置为序列的起始值
- php - Oauth 登录后将用户发送到 REQUEST_URI
- chef-infra - 刀客户端列表未能通过密钥 {key} 以厨师用户身份向 {server_url} 进行身份验证
- python - Discord.py bot TypeError: __init__() 接受 1 个位置参数,但给出了 2 个
- tableau-api - Tableau:在具有空日期的文本表上添加条件标记
- r - R时间序列范围没有达到我的数据的结束日期