SQL语句练习实例之六人事系统中的缺勤（休假）统计

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。