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次的員工查詢出來。