復(fù)制代碼 代碼如下:
---這是一個人事系統(tǒng)中的示例,要求記錄一下員工的缺勤情況
---1.要在表中記錄一下缺勤計分,是對經(jīng)常缺勤者的一種處罰性計分
---規(guī)則:
---1.如果員工在一年內(nèi)的缺勤計分達(dá)到50,就會可以解雇該員工。
---2.如果員工缺勤連續(xù)超過一天,就視為長病假,這時,第二天,第三天及以后的天數(shù)都不會統(tǒng)計該員工的缺勤計分
----這些天也不算為缺勤。
create table absence
(
empId int not null,
absenceDate datetime not null,
reason nvarchar(100) not null,
severityPoints int
)
go
----2.如果員工缺勤連續(xù)超過一天,就視為長病假,這時,第二天,第三天及以后的天數(shù)都不會統(tǒng)計該員工的缺勤計分
----這些天也不算為缺勤。
---這個怎么實(shí)現(xiàn)呢?
---最簡單的方法,就是允許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.如果員工在一年內(nèi)的缺勤計分達(dá)到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.子查詢沒有按鈕要求檢查員工的缺勤計分在一年內(nèi)是否達(dá)到或超過50,它需要在where子名中進(jìn)行額外的日期范圍檢查
--修改之后的刪除
--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關(guān)于在刪除了員工之后,卻沒有刪除此員工的缺勤記錄,需要顯式刪除或隱式刪除,隱式刪除可以添加級聯(lián)刪除。
---假設(shè):如果在缺勤期間,員工不會調(diào)換部門,則可以使用以下語句來提高性能
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語句只會把第二個星期的五個工作日
----設(shè)為長病假,第一周的周五與第三周的周一,都將視為缺勤,并計算缺勤計分。所以上面的update中的子查詢需要對
---遺漏的一系列日期做額外的處理。
---方法:
---1.對預(yù)先安排好的休息日(如周六,周日,五一,十一等)添加一個缺勤計分為0的代碼,可以避免周未與節(jié)假日的問題。
--對于這類問題需要新增一張表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
您可能感興趣的文章:- SQL語句練習(xí)實(shí)例之五 WMS系統(tǒng)中的關(guān)于LIFO或FIFO的問題分析
- SQL語句練習(xí)實(shí)例之四 找出促銷活動中銷售額最高的職員
- SQL語句練習(xí)實(shí)例之一——找出最近的兩次晉升日期與工資額
- SQL語句練習(xí)實(shí)例之二——找出銷售冠軍
- SQL語句練習(xí)實(shí)例之三——平均銷售等待時間
- SQL語句練習(xí)實(shí)例之七 剔除不需要的記錄行