我想计算在特定时间登录系统的员工总数,以及每个员工的总工作时间。
我们有一个登录系统,该系统将数据存储在以下数据结构中:
1.EmpId
2.Status
3.Created
以上数据存储在下表中:
EmpId Status Created
1 In 2019-10-23 12:00:00
1 Out 2019-10-23 12:45:45
2 In 2019-10-23 14:25:40
1 In 2019-10-23 18:45:45
2 Out 2019-10-23 20:50:40
2 In 2019-10-24 1:27:24
3 In 2019-10-24 2:45:45
In之后是Out,反之亦然。员工的工作时间分布在几天之内,我的意思是进出工作可能跨越几天。
我需要实现以下内容:
如何计算在特定时间登录的员工人数,例如“ 2019-10-23 14:12:45”。
自开始以来如何计算所有员工的总工作时间?
您可以使用此查询获取在特定时间注册的员工:
SELECT *
FROM (
SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
FROM table_name
WHERE Status = 'In'
) t WHERE '2019-10-23 14:12:45' BETWEEN [In] AND [Out]
OR '2019-10-23 14:12:45' >= [In] AND [Out] IS NULL;
...以及以下查询以获取每个员工的总工作时间作为TIME
值:
SELECT EmpID, CONVERT(TIME(0), DATEADD(S, ISNULL(SUM(DATEDIFF(S, [In], [Out])), 0), 0), 108) AS work_hours
FROM (
SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
FROM table_name
WHERE Status = 'In'
) t GROUP BY EmpID
您还可以在查询上定义和使用CTE(公用表表达式),而不是子选择,以获取带有登录时间和注销时间作为列的平面表:
WITH Employees(EmpID, [In], [Out]) AS
(
SELECT EmpID, Created AS [In], LEAD(Created) OVER (PARTITION BY EmpID ORDER BY Created ASC) AS [Out]
FROM table_name
WHERE Status = 'In'
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句