# 根据员工上下班的等级计算考勤率和迟到次数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(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,'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

/*

--------------- --------------------------------------- ----------- ----------- -----------
zhangsan        0.60                                    2           2           2

(2 行受影响)

• 0
点赞
• 2
评论
• 1
收藏
• 打赏
• 扫一扫，分享海报

01-19 797

10-22 1101
01-21 4823
01-21 1740
11-27 6252

lintf1986

¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。