根据员工上下班的等级计算考勤率和迟到次数SQL语句

 select 1 as ManaulId,'admin' as UserId,'2008-12-12 08:30:00' as SignTime,'高手帮忙啊'as SignDesc,'10' as SignTag
into tb5
insert into tb5 values(2,'admin','2008-12-12 09:00:00', '急急','21')
insert into tb5 values(3,'zhangsan','2008-12-12 09:00:00','急急','11')
insert into tb5 values(4,'zhangsan','2008-12-12 17:00:00','急急','21')
insert into tb5 values(5,'admin','2008-12-13 09:00:00','急急','11')
insert into tb5 values(6,'admin','2008-12-13 17:30:00','急急','20')

insert into tb5 values(6,'admin','2008-12-15 08:32:00','急急','11')
insert into tb5 values(6,'admin','2008-12-15 17:30:00','急急','20')
insert into tb5 values(6,'zhangsan','2008-12-15 09:30:00','急急','11')
insert into tb5 values(6,'zhangsan','2008-12-15 17:20:00','急急','21')
insert into tb5 values(6,'admin','2008-12-16 08:30:00','急急','10')
insert into tb5 values(6,'admin','2008-12-16 17:35:00','急急','21')
insert into tb5 values(6,'zhangsan','2008-12-16 07:30:00','急急','10')
insert into tb5 values(6,'zhangsan','2008-12-16 17:40:00','急急','20')

declare @starttime datetime
declare @endtime datetime
set @starttime='2008-12-09 08:00:00' --开始统计的时间
set @endtime='2008-12-16 23:00:00' --结束统计的时间

declare @morningtime varchar(20)
declare @afternoontime varchar(20)
set @morningtime='08:30:00' --开始统计的时间
set @afternoontime='17:30:00' --结束统计的时间

declare @workdatenum int --总的工作日数
select @workdatenum=(DATEPART(wk,@endtime)-DATEPART(wk,@starttime))*5 --相隔的周数
+(case when DATEPART(dw,@endtime)=7 and DATEPART(dw,@endtime)=1 then 5 else DATEPART(dw,@endtime)-1 end)
-(case when DATEPART(dw,@starttime)=7 and DATEPART(dw,@starttime)=1 then 5 else DATEPART(dw,@starttime)-1 end)
--DATEPART(dw,@endtime)为1是星期日，2为星期一，3为星期二，4为星期三，5为星期四，6为星期五，7为星期6

select distinct n.UserId as 用户名,
cast((select cast(count(distinct convert(varchar(20),SignTime,102)) as numeric(10,2))
/cast(@workdatenum as numeric(10,2)) from tb5
where SignTime>=@starttime and SignTime<=@endtime
and datepart(dw,SignTime)>1 and datepart(dw,SignTime)<7 and UserId=n.UserId)as decimal(5,2)) as 考勤率,
a.laternum as  迟到次数,b.earlynum as 早退次数,
c.report as 旷工次数
from tb5 n
left outer join
(select UserId,count(*) as laternum from tb5 where SignTag='11'
and datepart(dw,SignTime)>1 and datepart(dw,SignTime)<7
group by UserId) a
on a.UserId=n.UserId
left outer join
(select UserId,count(*) as earlynum from tb5 where SignTag='21'
and datepart(dw,SignTime)>1 and datepart(dw,SignTime)<7
group by UserId) b
on a.UserId=b.UserId
left outer join
(select UserId,@workdatenum-
count(distinct convert(varchar(20),SignTime,102)) as report from tb5
where datepart(dw,SignTime)>1 and datepart(dw,SignTime)<7
group by UserId) c
on c.UserId=a.UserId

/*
用户名          考勤率                                  迟到次数    早退次数    旷工次数
--------------- --------------------------------------- ----------- ----------- -----------
admin           0.60                                    1           2           2
zhangsan        0.60                                    2           2           2

(2 行受影响)