总工时
总工时
提问于 2013-07-03 23:20:18
log_date log_time emp_id emp_name log_action
2013-06-16 08:48:48.0000000 30170 Sarah John 1
2013-06-16 16:48:48.0000000 30170 Sarah John 4
2013-06-15 07:18:48.0000000 30160 Paula Fred 1
2013-06-15 16:38:48.0000000 30160 Paula Fred 4我有一个包含上述信息的日志表。我有一个查询来计算每个员工的每日工作时间，并将其存储在另一个小数字段中。问题是时间不对，如果一名员工在同一天打卡/打卡不止一次，就会增加工时。我希望它只说明第一次有效的打卡/打卡。
Select DISTINCT emp_id,log_date,SUM(datediff(Minute,stm, etm))/60.0
as wrk_hrs from
(
SELECT
emp_id, log_date,
log_time as etm,
(
SELECT top 1 log_time
FROM tblLogs
WHERE log_action = '1' and log_time <= a.log_time and emp_id=a.emp_id and a.log_date=log_date order by log_time desc
) as stm
FROM tblLogs as a
WHERE log_action = '4'
)as a
where stm is not null
group by log_date,emp_id,a.stm,a.etm;有没有一种更准确的方法来计算小时和分钟，并将它们存储起来以供进一步计算。我需要超过一段时间和总每周小时的报告。
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-07-03 23:54:35
SELECT StartLog.emp_id, StartLog.log_date, SUM(datediff(Minute,stm, etm))/60.0
FROM (
SELECT emp_id, log_date, MIN(log_time) AS stm
FROM tblLogs
WHERE log_action = '1'
GROUP BY emp_id, log_date
) AS StartLog
INNER JOIN
(
SELECT emp_id, log_date, MIN(log_time) AS etm
FROM tblLogs
WHERE log_action = '4'
GROUP BY emp_id, log_date
) AS EndLog
ON StartLog.emp_id = EndLog.emp_id AND StartLog.log_date = EndLog.log_dateStack Overflow用户
发布于 2013-07-03 23:42:33
由于您只需要每个员工日操作的第一个条目：
SELECT
[log_date],
[emp_id],
[emp_name],
[1] AS [first_log_in],
[4] AS [first_log_out],
DATEDIFF(minute,[1],[4])/60.0 AS [hours]
FROM @tblLogs
PIVOT(MIN(log_time) FOR log_action IN ([1],[4])) p这假设您的日志数据状态良好。如果你需要在打卡之前检查打卡，或者在打卡之前检查多个打卡，那是一个完全不同的问题。
Stack Overflow用户
发布于 2013-07-04 10:20:21
如果你想要更高的精确度，请切换到秒：
Select emp_id,log_date,SUM(datediff(second,stm, etm))/3600.0
as wrk_hrs from
(
SELECT
emp_id, log_date,
log_time as etm,
(
SELECT top 1 log_time
FROM tblLogs
WHERE log_action = '1' and log_time <= a.log_time and emp_id=a.emp_id and a.log_date=log_date order by log_time desc
) as stm
FROM tblLogs as a
WHERE log_action = '4'
)as a
where stm is not null
group by log_date,emp_id,a.stm,a.etm;注意:在使用group by时，distinct通常是完全多余的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17451909
复制相关文章
相似问题
腾讯云开发者
