计算刷卡记要

计算刷卡记录
表一：
 员工姓名 员工工号 刷卡时间
 蔡燕 20513 2014-6-27 18:20:45
蔡燕 20513 2014-6-27 18:20:47
蔡燕 20513 2014-6-28 8:14:54
蔡燕 20513 2014-6-28 8:19:54
蔡燕 20513 2014-6-28 18:14:54
艾云东 11304 2014-06-27 08:04:18
艾云东 11304 2014-06-27 16:40:50
艾云东 11304 2014-06-27 17:40:50

我筛选出每个员工，每天的上班情况，7点到12点算上班打卡，16点以后算下班打卡，多次刷卡取最早的打卡时间为上班卡，取最晚时间为下班卡，如果在该时间段未存在打卡记录即显示未打卡，得出类似下表：
 日期 员工姓名                     上班   下班
2014-6-27         秦燕         未打卡   2014-6-27 18:20:47
2014-6-28         秦燕          2014-6-28 8:14:54   2014-6-28 18:14:54
……
------解决思路----------------------

SELECT CONVERT(VARCHAR(10),刷卡时间,120)[日期],员工姓名,员工工号
	,ISNULL(CONVERT(VARCHAR(50),MIN(CASE WHEN 刷卡时间
		BETWEEN CONVERT(VARCHAR(10),刷卡时间,120)+' 07:00:00' AND CONVERT(VARCHAR(10),刷卡时间,120)+' 12:00:00'
		THEN 刷卡时间 END),120),'未打卡')[上班]
	,ISNULL(CONVERT(VARCHAR(50),MAX(CASE WHEN 刷卡时间>=CONVERT(VARCHAR(10),刷卡时间,120)+' 16:00:00' THEN 刷卡时间 END),120),'未打卡')[下班]
FROM 表一
GROUP BY CONVERT(VARCHAR(10),刷卡时间,120),员工姓名,员工工号

------解决思路----------------------

CREATE TABLE #TB
(UserName VARCHAR(50),UserNr varchar(50),CreateDate DATETIME)
GO
INSERT INTO #TB
        ( UserName, UserNr, CreateDate )
SELECT '蔡燕','20513','2014-6-27 18:20:45'
UNION ALL
SELECT '蔡燕','20513','2014-6-27 18:20:47'
UNION ALL
select '蔡燕','20513','2014-6-28 8:14:54'
UNION ALL
select '蔡燕','20513','2014-6-28 8:19:54'
UNION ALL
select '蔡燕','20513','2014-6-28 18:14:54'
UNION ALL
select '艾云东','11304','2014-06-27 08:04:18'
UNION ALL
select '艾云东','11304','2014-06-27 16:40:50'
UNION ALL
select '艾云东','11304','2014-06-27 17:40:50'
GO


SELECT  CONVERT(VARCHAR(10), CreateDate, 120) AS [日期] ,
        UserName AS [员工] ,
        UserNr AS [工号] ,
        CASE WHEN RIGHT(CONVERT(VARCHAR(20), MIN(CreateDate), 120), 8) BETWEEN '07:00:00'
                                                              AND
                                                              '12:00:00'
             THEN CONVERT(VARCHAR(20), MIN(CreateDate), 120)
             ELSE '未打卡'
        END AS [上班] ,
        CASE WHEN RIGHT(CONVERT(VARCHAR(20), MAX(CreateDate), 120), 8) >= '16:00:00'
             THEN CONVERT(VARCHAR(20), MAX(CreateDate), 120)
             ELSE '未打卡'
        END AS [下班]
FROM    #TB
GROUP BY UserName ,
        UserNr ,
        CONVERT(VARCHAR(10), CreateDate, 120)