MsSql教程网-SQL语句练习实例之六 人事系统中的缺勤休假统计

复制代码 代码如下:

—这是一个人事系统中的示例,要求记录一下员工的缺勤情况

—1.要在表中记录一下缺勤计分,是对经常缺勤者的一种处罚性计分

—规则:

—1.如果员工在一年内的缺勤计分达到50,就会可以解雇该员工。

—2.如果员工缺勤连续超过一天,就视为长病假,这时,第二天,第三天及以后的天数都不会统计该员工的缺勤计分

—-这些天也不算为缺勤。

create table absence

(

empId int not null,

absenceDate datetime not null,

reason nvarchar(100) not null,

severityPoints int

)

go

—-2.如果员工缺勤连续超过一天,就视为长病假,这时,第二天,第三天及以后的天数都不会统计该员工的缺勤计分

—-这些天也不算为缺勤。

—这个怎么实现呢?

—最简单的方法,就是允许severityPoint允许为0

—更新 ‘长病假’,同时缺勤计分为0

update absence set severityPoints=0,reason=’长病假’

where exists (select * from absence as a where absence.empId=a.empId

and absence.absenceDate=dateadd(d,-1,a.absenceDate)

)

–1.如果员工在一年内的缺勤计分达到50,就会可以解雇该员工。

select empid,SUM(severityPoints) as totalPoints

from absence

group by empId

–删除

–delete from employee where empid =(select a.empid from absence where a.empid=employee.empid group by a.empid having sum(severityPoints)>=50)



—上面的语句中是否存在Bug呢?

—有。

—1.子查询没有按钮要求检查员工的缺勤计分在一年内是否达到或超过50,它需要在where子名中进行额外的日期范围检查

–修改之后的删除

–delete from employee where empid =(select a.empid from absence where a.empid=employee.empid

–and absenceDate between dateadd(d,-365,getdate()) and getdate()

–group by a.empid having sum(severityPoints)>=50)



–2关于在删除了员工之后,却没有删除此员工的缺勤记录,需要显式删除或隐式删除,隐式删除可以添加级联删除。

—假设:如果在缺勤期间,员工不会调换部门,则可以使用以下语句来提高性能

update absence set severityPoints=0,reason=’长病假’

where exists (select * from absence a where a.empId=absence.empId and

dateadd(d,-1,absence.absenceDate)=a.absencedate

)

——-第三,再考虑以下这种情况:

—对于跨星期的长病假,例如:如果你在周未生病,对公司来说是没有问题的,因为周未一般是不用上班的。

—接着你在第二个星期一个星期都缺勤了,第三个星期的星期一也缺勤了,上面的update语句只会把第二个星期的五个工作日

—-设为长病假,第一周的周五与第三周的周一,都将视为缺勤,并计算缺勤计分。所以上面的update中的子查询需要对

—遗漏的一系列日期做额外的处理。

—方法:

—1.对预先安排好的休息日(如周六,周日,五一,十一等)添加一个缺勤计分为0的代码,可以避免周未与节假日的问题。

–对于这类问题需要新增一张表calendar用来计算员工的工作 。

create table calendar

(

calendarDate datetime, –日期

Weeks int, –第几周

weekday varchar(20),–星期几

)

select empId,SUM(severitypoints) as score

from absence a ,calendar b

where b.calendarDate=a.absenceDate and a.absenceDate between DATEADD(d,-365,getdate())

and GETDATE()

group by empId

having SUM(a.severitypoints)>=50

go

drop table absence

drop table calendar

共享资源网提供最优质的资源集合。
共享资源网 » MsSql教程网-SQL语句练习实例之六 人事系统中的缺勤休假统计

共享资源网提供最优质的资源集合

充值中心 开启会员