求解:时间比较问题

[foxkla]

求解:

现在又如下两张表

表manulSign 签卡时间表

SignId UserId SignTime
1 admin 2007-12-06 07:36:14.000
2 admin 2007-12-06 10:36:29.057

表workTime 标准上班时间表

WorkTimeId OnDutyTime
1 8:30:00

要用签卡时间SignTime和OnDutyTime做比较 统计迟到人数 怎么转换 怎么比较?

[htl258_Tony]

下班范围呢？

[百年树人]

---测试数据---
if object_id('[manulSign]') is not null drop table [manulSign]
go
create table [manulSign]([SignId] int,[UserId] varchar(5),[SignTime] datetime)
insert [manulSign]
select 1,'admin','2007-12-06 07:36:14.000' union all
select 2,'admin','2007-12-06 10:36:29.057'
if object_id('[workTime]') is not null drop table [workTime]
go
create table [workTime]([WorkTimeId] int,[OnDutyTime] varchar(10))
insert [workTime]
select 1,'08:30:00'
 
---查询---
select a.*,
case when  convert(varchar(8),SignTime,108)>b.OnDutyTime then '迟到' else '正常' end
from manulSign a,
workTime b

---结果---
SignId      UserId SignTime                
----------- ------ ----------------------- ----
1           admin  2007-12-06 07:36:14.000 正常
2           admin  2007-12-06 10:36:29.057 迟到

(2 行受影响)

[dawugui]

create table [manulSign]([SignId] int,[UserId] varchar(5),[SignTime] datetime)
insert [manulSign]
select 1,'admin','2007-12-06 07:36:14.000' union all
select 2,'admin','2007-12-06 10:36:29.057'
if object_id('[workTime]') is not null drop table [workTime]
go
create table [workTime]([WorkTimeId] int,[OnDutyTime] varchar(10))
insert [workTime]
select 1,'08:30:00'

select 迟到人数 = count(1) from  manulSign m, workTime n where convert(varchar(8) , m.SignTime , 114) > n.OnDutyTime 

drop table manulSign , workTime

/*
迟到人数        
----------- 
1

（所影响的行数为 1 行）
*/

[心中的彩虹]

if object_id('[manulSign]') is not null drop table [manulSign]
go
create table [manulSign]([SignId] int,[UserId] varchar(5),[SignTime] datetime)
insert [manulSign]
select 1,'admin','2007-12-06 07:36:14.000' union all
select 2,'admin','2007-12-06 10:36:29.057'
if object_id('[workTime]') is not null drop table [workTime]
go
create table [workTime]([WorkTimeId] int,[OnDutyTime] varchar(10))
insert [workTime]
select 1,'08:30:00'


select count(SignId) 迟到人数 from manulSign,workTime
where SignTime>=cast('2007-12-06 '+OnDutyTime as datetime)

select UserId,count(SignId) 迟到次数 from manulSign,workTime
where convert(char(8),SignTime,114)>=OnDutyTime
group by UserId

--结果
迟到人数
1