求个sql 月统计
考勤记录表 : kqjl 表 id ,cardID (IC卡号) ,kqsj (考勤时间)
另上班时间8点 下班时间5点 上班刷一次卡,下班刷一次卡,
迟到:8点到9:00 算迟到
早退:下午早于5点算早退
缺勤: 9点 以后刷卡算缺勤 ,当天未刷卡算缺勤
统计某月结果: 比如 2011-3-01 -- 2011-4-01 的出勤情况如下 :
卡号 正常 迟到 早退 缺勤
01 15 2 1 3
考勤记录表 : kqjl 表 id ,cardID (IC卡号) ,kqsj (考勤时间)
另上班时间8点 下班时间5点 上班刷一次卡,下班刷一次卡,
迟到:8点到9:00 算迟到
早退:下午早于5点算早退
缺勤: 9点 以后刷卡算缺勤 ,当天未刷卡算缺勤
统计某月结果: 比如 2011-3-01 -- 2011-4-01 的出勤情况如下 :
卡号 正常 迟到 早退 缺勤
01 15 2 1 3
(case when sj1<='08:00' and sj2>='17:00' then 1 else 0 end) 正常,
(case when sj1>'08:00' then 1 else 0 end) 迟到,
(case when sj2<'17:00' then 1 else 0 end) 早退
from(
select a.id,convert(varchar(5),a.kqsj,108) sj1,convert(varchar(5),b.kqsj,108) sj2
from kqjl a inner join kqjl b on a.id=b.id and convert(varchar(10),a.kqsj,120)=convert(varchar(10),b.kqsj,120) and a.kqsj<b.kqsj
)T
使用case when 进行分组统计,大致为:select id ,
sum(case when datepart(hh,时间字段) = 8 then 1 else 0 end) [迟到],
...
from tb
group by id
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
kqjl 表的数据:
id cardID kqsj
7 EB 1D FD C0 2011-03-26 17:56:53.000
8 EB 1D FD C0 2011-03-26 08:00:54.000
9 EB 1D FD C0 2011-03-29 14:25:08.000
10 EB 1D FD C0 2011-03-29 07:37:15.000
11 EB 1D FD C0 2011-03-28 08:37:15.000
12 EB 1D FD C0 2011-03-28 17:37:15.000
13 DD 1D FD C0 2011-03-28 07:37:15.000
14 DD 1D FD C0 2011-03-28 17:37:15.000
/// 结果应该:
卡号 正常 迟到 早退
EB 1D FD C0 1 1 1
DD 1D FD C0 1
select CardID,(case when (正常上班=1 and 正常下班=1) then 1 else 0 end) as 正常,迟到,早退
from
(
SELECT
CardID
,sum(case when DATEPART(hh,kqsj)<=8 then 1 else 0 end) as 正常上班
,sum(case when DATEPART(hh,kqsj)>=17 then 1 else 0 end) as 正常下班
,sum(case when (DATEPART(hh,kqsj)<=9 and DATEPART(hh,kqsj)>8 ) then 1 else 0 end) as 迟到
,sum(case when (DATEPART(hh,kqsj)<=17 and DATEPART(hh,kqsj)>12 ) then 1 else 0 end) as 早退
FROM kqjl where [PayTime]>'2011-03-01' and [kqsj]<='2011-03-31'
group by CardID
) b上面代码只供参考,未考虑以下特殊情况:
1、全天无打卡;
2、9点至12点刷卡的情况;
3、12点之后才来上班打卡的情况
4、12点之前就下班打卡的情况
5、多次刷卡的情况
建议:
1、先对打卡记录进行整理,判断出每天条打卡记录是上班,还是下班;
2、标记出每条记录的状态,如上班正常、下班正常,上班迟到、下班早退;
3、只有准确的明细记录,才能统计出正常的结果。