1. 关于考勤系统 mssql2008
这个问题比较复杂，设计到时间点的界定
--建立辅助表
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'worktime' AND type = 'U')
DROP TABLE worktime
GO
create table worktime(id int,workfrom smalldatetime, workend smalldatetime, flag int)
go
declare @a smalldatetime
set @a=0
insert into worktime select 1,dateadd(mi,08*60,@a),dateadd(mi,12*60,@a),1
insert into worktime select 2,dateadd(mi,12*60,@a),dateadd(mi,13*60,@a),0
insert into worktime select 2,dateadd(mi,13*60,@a),dateadd(mi,17*60,@a),1
insert into worktime select 3,dateadd(mi,17*60,@a),dateadd(mi,18*60,@a),0
insert into worktime select 3,dateadd(mi,18*60,@a),dateadd(mi,20*60,@a),2
insert into worktime select 4,dateadd(mi,20*60,@a),dateadd(mi,22.5*60,@a),1
insert into worktime select 5,dateadd(mi,22.5*60,@a),dateadd(mi,23.5*60,@a),0
insert into worktime select 5,dateadd(mi,23.5*60,@a),dateadd(mi,3.5*60,@a+1),1
insert into worktime select 6,dateadd(mi,3.5*60,@a+1),dateadd(mi,4.5*60,@a+1),0
insert into worktime select 6,dateadd(mi,4.5*60,@a+1),dateadd(mi,5*60,@a+1),1
insert into worktime select 7,dateadd(mi,5*60,@a+1),dateadd(mi,8*60,@a+1),2
----查询
select gzbhid,日期,convert(decimal(3,1),(isnull(sum(datediff(mi,wf1,we)),0)-sum(d1))/60.0) 上班时间,
convert(decimal(3,1),(isnull(sum(datediff(mi,wf2,we)),0)-sum(d2))/60.0) 加班时间
from
(select gzbhid,wf1,(case when wf1>wf2 then null else wf2 end) wf2,
(case when datepart(hh,we)<8 then we-1 else we end) we
,(select isnull(sum(datediff(mi,workfrom,workend)),0) from worktime where flag=0
and dateadd(day,datediff(day,workfrom,wf1),workfrom)>wf1
and dateadd(day,datediff(day,workend,we),workend)<we) d1
,(select isnull(sum(datediff(mi,workfrom,workend)),0) from worktime where flag=0
and dateadd(day,datediff(day,workfrom,wf2),workfrom)>wf2
and dateadd(day,datediff(day,workend,we),workend)<we) d2
,convert(varchar(10),(case when datepart(hh,we)<8 then we-1 else we end),20) 日期
from (select *,
(select top 1 (case when dateadd(day,datediff(day,workend,x.dates),workfrom)<x.dates then
(case when datepart(hh,x.dates)<8 then dates-1 else dates end)
else dateadd(day,datediff(day,workfrom,x.dates),workfrom) end) from worktime where flag=1
and dateadd(day,datediff(day,workend,x.dates2),workfrom)<x.dates2
and dateadd(day,datediff(day,workend,x.dates),workend)>x.dates order by workfrom) wf1
,(select top 1 (case when dateadd(day,datediff(day,workend,x.dates),workfrom)<x.dates then
(case when datepart(hh,x.dates)<8 then dates-1 else dates end)
else dateadd(day,datediff(day,workfrom,x.dates),workfrom) end) from worktime where flag=2
and dateadd(day,datediff(day,workend,x.dates2),workfrom)<x.dates2
and dateadd(day,datediff(day,workend,x.dates),workend)>x.dates order by workfrom) wf2
,(select top 1 (case when flag=0 then dateadd(day,datediff(day,workend,x.dates2),workfrom)
else x.dates2 end) from worktime where
dateadd(day,datediff(day,workend,x.dates2),workend)>x.dates2 order by workend) we
from (select *, (select top 1 dates from #kq b where gzbhid=a.gzbhid and arrid=27361 and dates>a.dates
and not exists (select 1 from #kq where arrid=27357 and gzbhid=a.gzbhid and dates>a.dates
and dates<b.dates) order by dates) dates2
from #kq a ) x
where arrid=27357
) x2
) x3
group by 日期,gzbhid
order by 日期,gzbhid
2. SQL如何判断迟到
给你一个思路吧
1，先把两个表串起来
2，按照 日期 分组，取一个最小的打卡时间
3，case 语句，判断取出来的时间 和 9：30的大小关系。
3. SQL 筛选数据问题(异常打卡记录筛选)
select 姓名,编号,datepart(day,打卡时间) from 表
group by 姓名,编号,datepart(day,打卡时间)
having count(*)<>2
这样就好了，我们公司现在做的就是考勤系统，如果有问题可以HI我继续交流
4. 怎么用SQL语句查出打卡异常信息
你的原表是什么样子的？有什么字段？上班时间和下班时间怎么确定？
显示有多少条记录这个好弄，前面的应该也不是很困难，可是没有字段和字段含义怎么写啊？
5. SQL如何判断打卡记录是否异常
casewhen
日期字段in（between7.30and8.00，between12.00and12.30，between13.30and14.00，between18and18.30）then‘正常’else‘异常’end，
大概就是这么写吧，这个字段，把时间字段处理一下应该就行
6. Sql 如何将表中的考勤记录，按时间段统计次数，不分工号，只按日期和时间段
select convert(char(10),iotime,120) as 日期,sum(case when datediff(Hour,convert(char(10),iotime,120),iotime)>=15 then 1 else 0) as 15点之后的个数,sum(case when datediff(Hour,convert(char(10),iotime,120),iotime)<15 then 1 else 0) as 15点之前的个数 from 表名 where 这的条件看自己的了，愿意一天或者一个时间段都可以了
7. sql 考勤数据 未打卡的日期问题
第一个是sqlserver的，第二个是oracle的，我这里建的t5是因为没有完整的表记录，取不到完整的时间，而你这里的表有完整的记录，所以select distinct substring(start_time,0,11) dt,plan_id,cardno from t5这句完全可以select distinct substring(start_time,0,11) dt,plan_id,cardno from tabname（tabname就是你上面那张表）来替代t5，这样可以取出完整的时间集合，总不会出现所有人上班时间都没来吧，呵呵。
createtablet5
(start_timevarchar(20),plan_idvarchar(1),cardNovarchar(10));
createtablet4
(
attend_idvarchar(4),plan_idvarchar(1),cardNovarchar(10),start_timevarchar(20),end_timevarchar(20)
);
insertintot4
select'4613','5','00009','2012-09-1108:37',null
unionall
select'4614','5','00009','2012-09-1308:53','2012-09-1318:01'
unionall
select'4615','5','00009','2012-09-1508:54','2012-09-1518:02'
unionall
select'4616','5','00009','2012-09-1708:30','2012-09-1718:03'
unionall
select'4617','5','00009','2012-09-1808:37','2012-09-1819:45'
unionall
select'4618','5','00009','2012-09-1908:37','2012-09-1919:01'
unionall
select'4619','5','00009','2012-09-2008:37','2012-09-2018:45'
unionall
select'4620','5','00009','2012-09-2108:37',null
unionall
select'4621','5','00009','2012-09-2308:37','2012-09-2318:35';
insertintot5
select'2012-09-1108:37','5','00009'
unionall
select'2012-09-1208:37','5','00009'
unionall
select'2012-09-1308:37','5','00009'
unionall
select'2012-09-1408:37','5','00009'
unionall
select'2012-09-1508:37','5','00009'
unionall
select'2012-09-1508:37','5','00009'
unionall
select'2012-09-1608:37','5','00009'
unionall
select'2012-09-1708:37','5','00009'
unionall
select'2012-09-1808:37','5','00009'
unionall
select'2012-09-1808:37','5','00009'
unionall
select'2012-09-2008:37','5','00009'
unionall
select'2012-09-2108:37','5','00009'
unionall
select'2012-09-2208:37','5','00009'
unionall
select'2012-09-2308:37','5','00009'
unionall
select'2012-09-2408:37','5','00009';
--sqlserver语句
selectt4.attend_id,
t1.plan_id,
t1.cardno,
casewhensubstring(t4.start_time,0,11)=t1.dtthent4.start_timeelset1.dt+'00:00'endstart_time,
casewhensubstring(t4.end_time,0,11)=t1.dtthent4.end_timeelset1.dt+'00:00'endend_time
from
(selectdistinctsubstring(start_time,0,11)dt,plan_id,cardnofromt5)t1
leftjoint4ont1.dt=substring(t4.start_time,0,11)andt1.cardno=t4.cardno
orderbyt1.dt;
--oracle
selectt4.attend_id,
t1.plan_id,
t1.cardno,
casewhensubstr(t4.start_time,0,10)=t1.dtthent4.start_timeelset1.dt||'00:00'endstart_time,
casewhensubstr(t4.end_time,0,10)=t1.dtthent4.end_timeelset1.dt||'00:00'endend_time
from
(selectdistinctsubstr(start_time,0,10)dt,plan_id,cardnofromt5)t1
leftjoint4ont1.dt=substr(t4.start_time,0,10)andt1.cardno=t4.cardno
orderbyt1.dt;
结果：
4613 5 00009 2012-09-1108:37 2012-09-1100:00
NULL 5 00009 2012-09-1200:00 2012-09-1200:00
4614 5 00009 2012-09-1308:53 2012-09-1318:01
NULL 5 00009 2012-09-1400:00 2012-09-1400:00
4615 5 00009 2012-09-1508:54 2012-09-1518:02
NULL 5 00009 2012-09-1600:00 2012-09-1600:00
4616 5 00009 2012-09-1708:30 2012-09-1718:03
4617 5 00009 2012-09-1808:37 2012-09-1819:45
4619 5 00009 2012-09-2008:37 2012-09-2018:45
4620 5 00009 2012-09-2108:37 2012-09-2100:00
NULL 5 00009 2012-09-2200:00 2012-09-2200:00
4621 5 00009 2012-09-2308:37 2012-09-2318:35
NULL 5 00009 2012-09-2400:00 2012-09-2400:00
有问题再追问吧，望采纳。
8. SQL，超过9点显示迟到，17半之前算早退，没有记录算旷工，全部完成算正常，SQL语句怎么写
SELECT a.员工姓名，
(SELECT CASE WHEN 考勤时间 > 9点 THEN '迟到' END from 考勤表 where 员工卡id=a.员工卡id and 考勤时间<当天12:00) as '早'，
(SELECT CASE WHEN 考勤时间 《 17点30 THEN '早退' END from 考勤表 where 员工卡id=a.员工卡id and 考勤时间>当天12:00) as '晚'，
(SELECT CASE WHEN COUNT(1)=0 then '是' ELSE '' END from 考勤表 where 员工卡id=a.员工卡id) as '旷工'，
from 员工表 a
没考虑重复打卡情况。
自己根据具体翻译一下吧，我的思路也就这样了。水平有限 ，勿喷。
9. sql语句,查询某个部门的员工考勤异常超过5次的员工,
select
*
from
（select
部门，
员工姓名
,
count(1)
as
'考勤异常数'
from
table
where
部门
=
'X'
and
考勤
=
'异常'
group
by
部门,
员工姓名）
t1
where
t1.考勤异常数
>
5;
---------------------------------------------------------------------------
sql解释：内层语句是统计某个部门（X）考勤有'异常'的员工及异常次数；
外层语句是将异常次数超过5次的员工查询出来。