‘壹’ sql统计一个月一共出勤多少天
出勤天数
select * from 考勤表 where (intkqstate<7 and outkqstate<7) and 日期 between '2009-1-1' and '2009-1-31' and 员工ID=" & ID
如果不查某个人的，就不用加员工ID这个条件
未出勤天数：
select * from 考勤表 where (intkqstate>6 and outkqstate>6) and 日期 between '2009-1-1' and '2009-1-31' and 员工ID=" & ID
‘贰’ 如何通过SQL语句进行考勤统计
selectnameas"名字",
max(decode(date_time,'2010/3/50:00:00',type))as"2010/3/50:00:00",
max(decode(date_time,'2010/3/60:00:00',type))as"2010/3/60:00:00",
max(decode(date_time,'2010/3/70:00:00',type))as"2010/3/70:00:00",
max(decode(date_time,'2010/3/80:00:00',type))as"2010/3/80:00:00"
fromtmp
groupbyname
orderbyname;
我用的是Oracle数据库；
我的表结构如下图所示：
‘叁’ SQL 统计每日上班打卡和下班打卡语句
这是在oracle数据库下做的，不知道你是什么数据库，给你提供一下思路
select
card_no,
attr_date,
to_char(atte_time,'HH24:MI')tim,
doorinout,
rn
from (
select
card_no,
attr_date,
atte_time,
doorinout,
ROW_NUMBER() OVER(PARTITION BY card_no,attr_date,doorinout ORDER BY atte_time asc) rn
from(
select
card_no,
(
case
when doorinout=1
then to_date(to_char(atte_time,'yyyy-MM-dd'),'yyyy-MM-dd')
when (doorinout=0 and to_number(to_char(atte_time,'HH24'))<3)
then (to_date(to_char(atte_time,'yyyy-MM-dd'),'yyyy-MM-dd')-1)
else to_date(to_char(atte_time,'yyyy-MM-dd'),'yyyy-MM-dd')
end
)attr_date,
atte_time,
doorinout
from AtteTime
) tmp order by card_no,atte_time asc,doorinout desc
) where rn < 5
‘肆’ sql语句实现考勤报表，sql语句怎样实现员工同一天只有两次考勤
两次考勤：select 员工ID,TRUNC(TIME,'d') FROM 考勤表 group by 员工ID,TRUNC(TIME,'d')
having count(*)=2;
漏打：select 员工ID,TRUNC(TIME,'d') FROM 考勤表 group by 员工ID,TRUNC(TIME,'d')
having count(*)<2;
‘伍’ SQL考勤统计语句，求助
select name,
sum(case when intime between '8:00' and '8:29' then 1 else 0 end ) as '迟到',
sum(case when intime between '8:30' and '9:00' then 1 else 0 end ) as '缺勤'
from table1
group by name
因为“8：30”是一个公用时间点，不能判别式迟到还是缺勤，
所以迟到我换成“8：29”了
‘陆’ 问一个考勤SQL语句的问题，想查询一整月考勤记录
select 员工ID,a.[1] 上班时间,b.[1] 下班时间,datediff(mm,b.[1] - a.[1]) 累计时间,
a.[2] 上班时间,b.[2] 下班时间,datediff(mm,b.[2] - a.[2]) 累计时间,.......
from
( select 员工ID,上班时间 from 考勤表
pivot ( min( 考勤时间) for datepart(dd, 考勤时间) in ( [1],[2],[3],................... )) as pvt ) a,
( select 员工ID,上班时间 from 考勤表
pivot ( mxn( 考勤时间) for datepart(dd, 考勤时间) in ( [1],[2],[3],................... )) as pvt ) b
where a.员工ID = b.员工ID
‘柒’ sql 考勤统计查询
select name,dept,count(*) 工作天数,sum(xbsj-sbsj) 工作累计时间,count(case when kqqk=1 then 1 end) 迟到天数,count(case when kqqk=2 then 1 end) 早退天数
from table1 join table2 on table1.uid=table2.uid
group by name,dept
‘捌’ sql语句,查询某个部门的员工考勤异常超过5次的员工,
select
*
from
（select
部门，
员工姓名
,
count(1)
as
'考勤异常数'
from
table
where
部门
=
'X'
and
考勤
=
'异常'
group
by
部门,
员工姓名）
t1
where
t1.考勤异常数
>
5;
---------------------------------------------------------------------------
sql解释：内层语句是统计某个部门（X）考勤有'异常'的员工及异常次数；
外层语句是将异常次数超过5次的员工查询出来。
‘玖’ SQL语言考勤打卡记录
首先要有一个工厂日历的表，列出所有工作日,至少一个字段：工作日 varchar(10)。
然后这样即可：
select id,
迟到次数=sum(case when timec>'08:00:00' tand timec<'09:00:00' then 1 else 0 end),
旷工次数=sum(case when timec>'09:00:00' or timec is null then 1 else 0 end),
打卡次数=sum(case when timec is null then 0 else 1 end)
from
(
SELECT * FROM 工厂日历 left join
(select id,
datec=convert(varchar(10),card_time,120),
timec=substring(convert(varchar,card_time,120),12,8)
from tablename
) a
on 工作日=DATEC
) b
group by ID