WorkDate User OnDuty OnStatus OffDuty Offstatus
2005-08-01 wang1 9:00 0 14:00 2
2005-08-01 wang2 9:01 1 17:02 0
2005-08-01 wang3 8:45 0 18:02 0
2005-08-02 wang4 9:02 1 14:02 2如上表结构,WorkDate为日期,User为员工名,OnDuty为上班时间,OnStatus为上班状态(0为正常,1为迟到),OffDuty为下班时间,Offstatus为下班状态(0为正常,2为早退)。
现要按人员分组统计在某一时间段员工的考勤情况,即迟到天数,早退天数,,,,
请大家帮忙看看??
2005-08-01 wang1 9:00 0 14:00 2
2005-08-01 wang2 9:01 1 17:02 0
2005-08-01 wang3 8:45 0 18:02 0
2005-08-02 wang4 9:02 1 14:02 2如上表结构,WorkDate为日期,User为员工名,OnDuty为上班时间,OnStatus为上班状态(0为正常,1为迟到),OffDuty为下班时间,Offstatus为下班状态(0为正常,2为早退)。
现要按人员分组统计在某一时间段员工的考勤情况,即迟到天数,早退天数,,,,
请大家帮忙看看??
select count(*),user from table where workdate between 2005-08-01 and 2005-08-02 and OnStatus=1 group by user
早退
select count(*),user from table where workdate between 2005-08-01 and 2005-08-02 and Offstatus=2 group by user
不知道是不是你要的
Create Table 表(WorkDate varchar(10),[User] varchar(10),OnDuty varchar(10),OnStatus varchar(10),OffDuty varchar(10),Offstatus varchar(10))
--插入数据
insert into 表
select '2005-08-01','wang1','9:00','0','14:00','2' union
select '2005-08-01','wang2','9:01','1','17:02','0' union
select '2005-08-01','wang3','8:45','0','18:02','0' union
select '2005-08-02','wang4','9:02','1','14:02','2'--测试语句
select [user],sum(cast(OnStatus as int)) as 迟到次数,sum(cast(offstatus as int))/2 as 早退次数 from 表
group by [user]
--删除测试环境
Drop Table 表/*--(所影响的行数为 4 行)user 迟到次数 早退次数
---------- ----------- -----------
wang1 0 1
wang2 1 0
wang3 0 0
wang4 1 1(所影响的行数为 4 行)
--*/
(WorkDate BETWEEN CONVERT(char(10), '2005-8-1', 120) AND CONVERT(char(10), '2005-8-22', 120))
AND (OnStatus = 1) GROUP BY [User]这个可以算出迟到者和迟到的次数,如:
迟到次数 [User]
2 wang1
4 wang2
2 wang3将where中的条件变成Offstatus=2就可以得出早退者和早退的次数
试
按照你的方法,如何算出不迟到也不早退的人的天数呢,也就是正常天数??
Create Table 表(WorkDate varchar(10),[User] varchar(10),OnDuty varchar(10),OnStatus varchar(10),OffDuty varchar(10),Offstatus varchar(10))
--插入数据
insert into 表
select '2005-08-01','wang1','9:00','0','14:00','2' union
select '2005-08-01','wang2','9:01','1','17:02','0' union
select '2005-08-01','wang3','8:45','0','18:02','0' union
select '2005-08-02','wang4','9:02','1','14:02','2'--测试语句
select [user], sum(cast(OnStatus as int)) as 迟到次数,
sum(cast(offstatus as int))/2 as 早退次数,
sum(case when (cast(OnStatus as int)+cast(offstatus as int))=0 then 1 else 0 end) as 正常天数
from 表
group by [user]
--删除测试环境
Drop Table 表/*--
(所影响的行数为 4 行)user 迟到次数 早退次数 正常天数
---------- ----------- ----------- -----------
wang1 0 1 0
wang2 1 0 0
wang3 0 0 1
wang4 1 1 0(所影响的行数为 4 行)
--*/