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 *
from T as tmp
where not exists(select 1 from T where 工号=tmp.工号 and 打卡日期=tmp.打卡日期 and 打卡时间<tmp.打卡时间)
and convert(datetime, 打卡时间, 108)<'1900-01-01 08:00:00'--result
工号 打卡日期 打卡时间
---------- ------------------------------------------------------ ----------
001 2006-09-08 00:00:00.000 07:40:09(1 row(s) affected)
where 打卡时>'08:00:00'
group by 工号,打卡日期
order by 打卡日期
> '08:00:00' THEN '迟到' ELSE '正常' END AS Expr2
FROM Table
GROUP BY 工号, 打卡日期
insert into @T values( '001', '2006-09-08', '07:40:09')
insert into @T values( '001', '2006-09-08', '08:09:00')
insert into @T values( '002', '2006-09-08', '08:09:00')
insert into @T values( '001', '2006-09-09', '08:09:09')select * from @T
select *,case when [打卡时间] >'08:00:00' then '迟到' else ''end
from
(
select [工号],min([打卡时间]) as [打卡时间] from @T group by [工号]
)
as t-------------select *
from
(
select [工号],min([打卡时间]) as [打卡时间] from @T group by [工号]
)
as t
where [打卡时间] >'08:00:00'
insert @ta
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 * from @ta a
where not exists(select 1 from @ta where 工号=a.工号 and 打卡日期=a.打卡日期 and 打卡时间<a.打卡时间)
and 打卡时间>'08:00:00'(4 行受影响)
工号 打卡日期 打卡时间
---- ---------- --------
002 2006.09.08 08:09:00
001 2006.09.09 08:09:09(2 行受影响)
--没必要
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'
union all select '001', '2006-09-09', '18:09:09'
select 工号,CONVERT(varchar(10),打卡日期,120),
case when min(打卡时间)>'08:00:00' then '迟到' else '正常' end
from t group by 工号, CONVERT(varchar(10),打卡日期,120)
(case when min(打卡时间)>'08:00:00' then '迟到' else '正常' end)状态
from t group by 工号, CONVERT(varchar(10),打卡日期,120)工号 日期 状态
001 2006-09-08 正常
002 2006-09-08 迟到
001 2006-09-09 迟到
go
create trigger_insertState
on table for insert
as
begin
declare @time nvarchar(8)
decalre @num nvarchar(16)
select @time=打卡时间,@num=工号 from inserted
if @time>'08:00:00'
update table set [state]='迟到' where 工号=@num
else
update table set [state]='正常' where 工号=@num
end