求某一段时间内人员不打卡的SQL

[wudixiaocaoren]

考勤表:
id cardid name zhiwu time
1 2001324 王玉宝 教师 2007-3-1 18:27:00
2 2001333 赵大傻 老师 2007-3-1 10:00:00

人员表：

卡号 姓名
2001324 王玉宝

现在想统计一下指定时间内，比如2007-3-1至2007-3-30日内，早上7：50到10:00之间，那个人没打卡？输出如下结果：

2001000 赵大红 无刷卡记录

[子陌红尘]

select
t.*
from
人员表 t
where
not exists(select
1
from
考勤表
where
cardid=t.cardid
and
time between '2007-03-01 00:00:00' and '2007-03-30 23:59:59'
and
convert(char(8),time,108) between '07:50:00' and '10:00:00')

[hrb2008]

CREATE PROCEDURE PROTEST
@BEGINDATE DATETIME,
@ENDDATE DATETIME
AS
SELECT cardid,name,签到时间 = CASE WHEN convert (datetime,RIGHT(CONVERT(CHAR(19),time,120),9)) >= convert (datetime,'7：50') and convert (datetime,RIGHT(CONVERT(CHAR(19),time,120),9)) <= convert (datetime,'10:00') THEN cast(time as varchar) ELSE '未签到' END FROM TB WHERE [time] BETWEEN @BEGINDATE AND @ENDDATE

[wudixiaocaoren]

对不起，我试了一下，可以运行，但没有指出是哪天没刷卡记录的呀？

[yahuu]

libin_ftsafe(子陌红尘：当libin告别ftsafe) 的格式这么标准
是在企业管理器中弄的？

[hxd001_810]

declare @startdate varchar(30)
declare @enddate varchar(30)
set @startdate = '2007-03-01'
set @enddate = '2007-03-30'
select cardid,姓名
from 考勤表
where not exists(select cardid 卡号,name 姓名
from 考勤表
where time between @startdate and @enddate AND
convert(char(8),time,108) between '07:50:00' and '10:00:00'
order by time)

[hxd001_810]

更正一下：
declare @startdate varchar(30)
declare @enddate varchar(30)
set @startdate = '2007-03-01'
set @enddate = '2007-03-30'
select a.cardid,a.姓名
from 考勤表 a
where not exists(select cardid 卡号,name 姓名
from 考勤表 b
where a.cardid = b.cardid AND
time between @startdate and @enddate AND
convert(char(8),time,108) between '07:50:00' and '10:00:00'
order by time)

[wudixiaocaoren]

上面大家都没有指出哪一天没有打卡呀？

[sp4]

这么简单，自己尝试写吧