--上下班时间表
create table t_timeRule(
id int identity(1,1) primary key,
dates varchar(5),
texts varchar(20)
)
insert into t_timeRule values('8:00','上午上班时间')
insert into t_timeRule values('12:00','上午下班时间')
insert into t_timeRule values('14:30','下午上班时间')
insert into t_timeRule values('17:30','下午下班时间')--考勤记录表
create table t_checkduty(
check_id int identity(1,1) primary key,
check_user_id int not null, --考勤人
check_type char(1),
--签到、签退 0:上午签到;1:上午签退; 2:下午签到;3:下午签退
check_date datetime, --考勤日期
)insert t_checkduty
select '1010',0,'2007-05-29'我现在要根据check_user_id算当月迟到,早退,未到多少次!!!
create table t_timeRule(
id int identity(1,1) primary key,
dates varchar(5),
texts varchar(20)
)
insert into t_timeRule values('8:00','上午上班时间')
insert into t_timeRule values('12:00','上午下班时间')
insert into t_timeRule values('14:30','下午上班时间')
insert into t_timeRule values('17:30','下午下班时间')--考勤记录表
create table t_checkduty(
check_id int identity(1,1) primary key,
check_user_id int not null, --考勤人
check_type char(1),
--签到、签退 0:上午签到;1:上午签退; 2:下午签到;3:下午签退
check_date datetime, --考勤日期
)insert t_checkduty
select '1010',0,'2007-05-29'我现在要根据check_user_id算当月迟到,早退,未到多少次!!!
insert t_checkduty
select '1010',0,getdate()
插入数据库中
来区分四种rule然后就可以根据check_user_id
通过
check_type--签到、签退 0:上午签到;1:上午签退; 2:下午签到;3:下午签退
和 check_date--考勤日期关联t_timeRule
求出早退,未到多少次!!!
check_type--签到、签退 0:上午签到;1:上午签退; 2:下午签到;3:下午签退也可以就用这个类型
insert t_checkduty
select '1010',0,'2007-05-29 8:00:00'select check_user_id,datediff(d,min(check_date),dateadd(m,1,min(check_date)))*4- Count(*) as Weidao ,
sum(case when check_type=0 then (case when check_date>Convert(nvarchar(11),check_date,120)+'8:00' then 1 else 0 end )
when check_type=2 then (case when check_date>Convert(nvarchar(11),check_date,120)+'14:30' then 1 else 0 end )
else 0 end) as Chidao,
sum(case when check_type=1 then (case when check_date<Convert(nvarchar(11),check_date,120)+'12:00' then 1 else 0 end )
when check_type=3 then (case when check_date<Convert(nvarchar(11),check_date,120)+'17:30' then 1 else 0 end )
else 0 end) as Zaotui
from t_checkduty
where check_user_id='1010' and year(check_date)='2007' and month(check_date)=5
group by check_user_id
insert t_checkduty select '1010',0,'2007-05-29 8:00:00'
insert t_checkduty select '1010',1,'2007-05-29 11:00:00'
insert t_checkduty select '1010',2,'2007-05-29 14:00:00'
insert t_checkduty select '1010',3,'2007-05-29 17:30:00'
insert t_checkduty select '1011',0,'2007-05-29 9:00:00'
insert t_checkduty select '1011',1,'2007-05-29 12:00:00'
insert t_checkduty select '1011',2,'2007-05-29 15:00:00'
insert t_checkduty select '1011',3,'2007-05-29 17:30:00'--result
1010 120 0 1
1011 120 2 0
select check_user_id,datediff(d,min(check_date),dateadd(m,1,min(check_date)))*4- Count(distinct check_date) as Weidao ,
sum(case when check_type=0 then (case when check_date>Convert(nvarchar(11),check_date,120)+dates then 1 else 0 end )
when check_type=2 then (case when check_date>Convert(nvarchar(11),check_date,120)+dates then 1 else 0 end )
else 0 end) as Chidao,
sum(case when check_type=1 then (case when check_date<Convert(nvarchar(11),check_date,120)+dates then 1 else 0 end )
when check_type=3 then (case when check_date<Convert(nvarchar(11),check_date,120)+dates then 1 else 0 end )
else 0 end) as Zaotui
from t_checkduty a INNER JOIN t_timeRule b ON a.check_type=(case texts when N'上午上班时间' then 0 when N'上午下班时间' then 1 when N'下午上班时间' then 2 when N'下午下班时间' then 3 end )
where year(check_date)='2007' and month(check_date)=5
group by check_user_id