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

赞 (0)

相关推荐

  • SQL语句练习实例之三——平均销售等待时间

    复制代码 代码如下: ---1.平均销售等待时间 ---有一张Sales表,其中有销售日期与顾客两列,现在要求使用一条SQL语句实现计算 --每个顾客的两次购买之间的平均天数 --假设:在同一个人在一天中不会购买两次 create table sales ( custname varchar(10) not null, saledate datetime not null ) go insert sales select '张三','2010-1-1' union select '张三','20

  • SQL语句练习实例之四 找出促销活动中销售额最高的职员

    复制代码 代码如下: ---找出促销活动中销售额最高的职员 ---你刚在一家服装销售公司中找到了一份工作,此时经理要求你根据数据库中的两张表得到促销活动销售额最高的销售员 ---1.一张是促销活动表 ---2.一张是销售客列表 create table Promotions ( activity nvarchar(30), sdate datetime, edate datetime ) insert Promotions select '五一促销活动','2011-5-1','2011-5-7

  • SQL语句练习实例之七 剔除不需要的记录行

    复制代码 代码如下: --相信大家肯定经常会把数据导入到数据库中,但是可能会有些记录行的所有列的数据是null,这为null的数据是我们不需要 --现在需要一个简单的查询来剔除掉这些为null的记录行. --假设表名为 emplyees --方法1. ---先把数据导入到数据库 ---其次: select * from sys.columns where object_id =(select object_id from sys.objects where name='EMPLYEEs') --

  • SQL语句练习实例之一——找出最近的两次晋升日期与工资额

    复制代码 代码如下: --程序员们在编写一个雇员报表,他们需要得到每个雇员当前及历史工资状态的信息, --以便生成报表.报表需要显示每个人的晋升日期和工资数目. --如果将每条工资信息都放在结果集的一行中,并让宿主程序去格式化它. --应用程序的程序员都是一帮懒人,他们需要在每个雇员的一行上得到当前 --和历史工资信息.这样就可以写一个非常简单的循环语句. ---示例: create table salaries ( name nvarchar(50) not null, sal_date da

  • SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析

    复制代码 代码如下: ---在仓储管理中经常会碰到的一个问题 一.关于LIFO与FIFO的简单说明 ---FIFO: First in, First out.先进先出. ---LIFO: Last in, First out.后进先出. --如货物A:本月1日购买10件,单价10元/件,3日购买20件,单价15元/件:10日购买10件,单价8元/件. --本月15日发货35件. --按FIFO先进先出,就是先购入的存货先发出,所以,先发1日进货的10件,再发3日进货的20件,最后发10日进货的5

  • SQL语句练习实例之二——找出销售冠军

    复制代码 代码如下: --销售冠军 --问题:在公司中,老板走进来,要一张每个地区销量前3名的销售额与销售员的报表 --- create table salesdetail ( Area int not null, Saler nvarchar(20) not null, SalerId int not null, Sales money not null ) insert salesdetail select 1,'张三',15,3000 union select 1,'赵一',16,3500

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

    复制代码 代码如下: ---这是一个人事系统中的示例,要求记录一下员工的缺勤情况 ---1.要在表中记录一下缺勤计分,是对经常缺勤者的一种处罚性计分 ---规则: ---1.如果员工在一年内的缺勤计分达到50,就会可以解雇该员工. ---2.如果员工缺勤连续超过一天,就视为长病假,这时,第二天,第三天及以后的天数都不会统计该员工的缺勤计分 ----这些天也不算为缺勤. create table absence ( empId int not null, absenceDate datetime

  • Mybatis基于注解形式的sql语句生成实例代码

    对其做了些优化,但此种sql生成方式仅适用于复杂程度不高的sql,所以实用性不是很高,仅仅是写着玩的,知道点mybatis的注解形式的使用方式,可能以后会逐渐完善起来.第一次写博客,写的简单点. package com.bob.config.mvc.mybatis; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retenti

  • Java使用Statement接口执行SQL语句操作实例分析

    本文实例讲述了Java使用Statement接口执行SQL语句操作的方法.分享给大家供大家参考,具体如下: Statement执行SQL语句: 1. 对数据库的曾删改操作时,使用stmt.executeUpdate(sql)  执行给定 SQL 语句,分别为 insert .update.delete. 2. 对数据库做查询时,直接使用 stmt.executeQuery(sql),返回结果可以为一个resultSet结果集. 首先做一些准备工作: ①对要进行操作的数据库表进行封装,比如说我的数

  • PHP实现的构造sql语句类实例

    本文实例讲述了PHP实现的构造sql语句类.分享给大家供大家参考,具体如下: /** * @package Database Class * @author injection (mail:injection.mail@gmail.com) * @version 1.0 */ @ini_set( 'display_errors',0 ); class DataBase{ private $mDb_host,$mAb_user,$mAb_pwd,$mConn_No; function DataBa

  • 实例解析Android系统中的ContentProvider组件用法

    实例解析Android系统中的ContentProvider组件用法

    ContentProvider为Android四大组件之一,主要用来应用程序之间的数据共享,也就是说一个应用程序用ContentProvider将自己的数据暴露出来,其他应用程序通过ContentResolver来对其暴露出来的数据进行增删改查. ContenProvider与ContentResolver之间的对话同过Uri(通用资源标识符),一个不恰当的比喻就好像浏览器要显示一个网页要有一个东西发送请求,这相当于ContentResolver,你要拿东西就要知道去哪里拿,你就得知道服务器的域

随机推荐