这是一个人事系统中的示例，要求记录一下员工的缺勤情况
|
---这是一个人事系统中的示例，要求记录一下员工的缺勤情况 ---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 |
