根据员工上下班的等级计算考勤率和迟到次数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 行受影响)

 

 

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

评论 2 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

lintf1986

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值