大家好,有如上图的公司考勤数据,17:30是正常下班时间,之后是的17:54分是加班开始的时间,21:00是加班结束时间,
目前需要取出每天最后两个时间值用于计算加班小时数。
但有个别员工加班结束时打了不止一次卡。导致取出时间有误,如下图请问怎样才能正确取出合适的时间?以下是我的测试代码,请指教一下,谢谢!
CREATE TABLE #tmp(UserID INT,CheckTime DATETIME)INSERT INTO #tmp
SELECT 1,'2016-01-16 21:00:19.000'
UNION ALL
SELECT 1,'2016-01-16 21:00:10.000'
UNION ALL
SELECT 1,'2016-01-16 17:54:30.000'
UNION ALL
SELECT 1,'2016-01-16 17:30:36.000'
UNION ALL
SELECT 2,'2016-01-15 21:01:19.000'
UNION ALL
SELECT 2,'2016-01-15 17:45:36.000'
UNION ALL
SELECT 2,'2016-01-15 17:31:30.000'
SELECT * FROM
(
SELECT
* ,ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY CheckTime DESC) AS orderNum
FROM #tmp
) AS tt
WHERE orderNum in (1,2)
DROP TABLE #tmp
目前需要取出每天最后两个时间值用于计算加班小时数。
但有个别员工加班结束时打了不止一次卡。导致取出时间有误,如下图请问怎样才能正确取出合适的时间?以下是我的测试代码,请指教一下,谢谢!
CREATE TABLE #tmp(UserID INT,CheckTime DATETIME)INSERT INTO #tmp
SELECT 1,'2016-01-16 21:00:19.000'
UNION ALL
SELECT 1,'2016-01-16 21:00:10.000'
UNION ALL
SELECT 1,'2016-01-16 17:54:30.000'
UNION ALL
SELECT 1,'2016-01-16 17:30:36.000'
UNION ALL
SELECT 2,'2016-01-15 21:01:19.000'
UNION ALL
SELECT 2,'2016-01-15 17:45:36.000'
UNION ALL
SELECT 2,'2016-01-15 17:31:30.000'
SELECT * FROM
(
SELECT
* ,ROW_NUMBER()OVER(PARTITION BY UserID ORDER BY CheckTime DESC) AS orderNum
FROM #tmp
) AS tt
WHERE orderNum in (1,2)
DROP TABLE #tmp
select userid,convert(varchar(10),CheckTime,120) CheckTime from #tmp where convert(varchar(8),CheckTime,108) > '17:45:00.000' group by userid,convert(varchar(10),CheckTime,120) having count(*) >= 2
) t2 on t1.userid = t2.userid and convert(varchar(10),t1.CheckTime,120) = t2.CheckTime where convert(varchar(8),t1.CheckTime,108) > '17:45:00.000'
group by t1.userid,convert(varchar(10),t1.CheckTime,120)
union all
select t1.userid,min(t1.checktime) checktime from #tmp t1 inner join (
select userid,convert(varchar(10),CheckTime,120) CheckTime from #tmp where convert(varchar(8),CheckTime,108) > '17:45:00.000' group by userid,convert(varchar(10),CheckTime,120) having count(*) >= 2
) t2 on t1.userid = t2.userid and convert(varchar(10),t1.CheckTime,120) = t2.CheckTime where convert(varchar(8),t1.CheckTime,108) > '17:45:00.000'
group by t1.userid,convert(varchar(10),t1.CheckTime,120)
order by userid,checktime