select *,(case when datediff(mi,打卡时间,'8:00:00')<0 then '迟到' else '正常' end) as 状态 from table
create table T(工号 varchar(10), 打卡日期 datetime, 打卡时间 varchar(10)) insert T select '001', '2006-09-08', '07:40:09' union all select '001', '2006-09-08', '08:09:00' union all select '002', '2006-09-08', '08:09:00' union all select '001', '2006-09-09', '08:09:09'select *,状态=(case when (convert(datetime, 打卡时间, 108)>'1900-01-01 08:00:00') then '迟到' else '正常' end) from T as tmp where not exists(select 1 from T where 工号=tmp.工号 and 打卡日期=tmp.打卡日期 and 打卡时间<tmp.打卡时间)
from table
create table T(工号 varchar(10), 打卡日期 datetime, 打卡时间 varchar(10))
insert T select '001', '2006-09-08', '07:40:09'
union all select '001', '2006-09-08', '08:09:00'
union all select '002', '2006-09-08', '08:09:00'
union all select '001', '2006-09-09', '08:09:09'select *,状态=(case when (convert(datetime, 打卡时间, 108)>'1900-01-01 08:00:00') then '迟到' else '正常' end) from T as tmp
where not exists(select 1 from T where 工号=tmp.工号 and 打卡日期=tmp.打卡日期 and 打卡时间<tmp.打卡时间)
工号 打卡日期 打卡时间 状态
---------- ------------------------------------------------------ ---------- ----
001 2006-09-08 00:00:00.000 07:40:09 正常
002 2006-09-08 00:00:00.000 08:09:00 迟到
001 2006-09-09 00:00:00.000 08:09:09 迟到(3 row(s) affected)