22,209
社区成员
发帖
与我相关
我的任务
分享
;WITH tb(姓名,时间)AS(
select N'卢青',CONVERT(DATETIME,'2016-12-26 7:53:58') union all
select N'卢青','2016-12-26 16:59:14' union all
select N'卢青','2016-12-27 7:59:24' union all
select N'卢青','2016-12-27 17:06:38' union all
select N'测试','2016-12-26 8:03:18' union all
select N'测试','2016-12-26 17:53:58'
)
SELECT t.姓名,t.CheckDay,MAX(CASE WHEN t.CheckInd=1 THEN CASE WHEN MinuteOfDay>=8*60+1/*8点零一分*/ THEN N'迟到' ELSE N'准时' END ELSE NULL END) AS [CheckIn]
,MAX(CASE WHEN t.CheckInd=t.checkcount THEN CASE WHEN MinuteOfDay<17*60 THEN N'早退' ELSE N'准时' END ELSE NULL END ) AS CheckOut
FROM (
SELECT *,CONVERT(VARCHAR,tb.时间,110) AS CheckDay,DATEPART(HOUR,tb.时间)*60+DATEPART(MINUTE,tb.时间) AS MinuteOfDay
,ROW_NUMBER()OVER(PARTITION BY tb.姓名 ,CONVERT(VARCHAR,tb.时间,110) ORDER BY tb.时间) AS CheckInd
,COUNT(0)OVER(PARTITION BY tb.姓名, CONVERT(VARCHAR,tb.时间,110)) checkcount
FROM tb
) AS t GROUP BY t.姓名,t.CheckDay
姓名 CheckDay CheckIn CheckOut
---- ------------------------------ ------- --------
卢青 12-26-2016 准时 早退
测试 12-26-2016 迟到 准时
卢青 12-27-2016 准时 准时
--测试数据
;WITH tab(姓名,时间)AS(
select '卢青','2016-12-26 7:53:58' union all
select '卢青','2016-12-26 16:59:14' union all
select '卢青','2016-12-27 7:59:24' union all
select '卢青','2016-12-27 17:06:38' union all
select '测试','2016-12-26 8:03:18' union all
select '测试','2016-12-26 17:53:58'
)
--测试数据结束
,temptime AS (
SELECT CONVERT(CHAR(10), 时间, 120) AS 本月时间
FROM tab
GROUP BY CONVERT(CHAR(10), 时间, 120)
)
SELECT 姓名 ,
本月时间 ,
( CASE WHEN ( ( SELECT MAX(时间)
FROM tab c
WHERE DATEDIFF(DAY, c.时间, 本月时间) = 0
AND c.姓名 = a.姓名
) > CONVERT(DATETIME, 本月时间 + ' 8:00:00') ) THEN '迟到'
ELSE ''
END
+ CASE WHEN ( ( SELECT MIN(时间)
FROM tab c
WHERE DATEDIFF(DAY, c.时间, 本月时间) = 0
AND c.姓名 = a.姓名
) < CONVERT(DATETIME, 本月时间 + ' 17:00:00') ) THEN '早退'
ELSE ''
END ) AS 出勤情况
FROM tab a
INNER JOIN temptime b ON DATEDIFF(DAY, 时间, 本月时间) = 0
GROUP BY a.姓名 ,
本月时间
结果: