---這是一個(gè)人事系統(tǒng)中的示例，要求記錄一下員工的缺勤情況
---1.要在表中記錄一下缺勤計(jì)分，是對(duì)經(jīng)常缺勤者的一種處罰性計(jì)分
---規(guī)則：
---1.如果員工在一年內(nèi)的缺勤計(jì)分達(dá)到50，就會(huì)可以解雇該員工。
---2.如果員工缺勤連續(xù)超過(guò)一天，就視為長(zhǎng)病假，這時(shí)，第二天，第三天及以后的天數(shù)都不會(huì)統(tǒng)計(jì)該員工的缺勤計(jì)分
----這些天也不算為缺勤。

create table absence
(
empId int not null,
absenceDate datetime not null,
reason nvarchar(100) not null,
severityPoints int

)
go
----2.如果員工缺勤連續(xù)超過(guò)一天，就視為長(zhǎng)病假，這時(shí)，第二天，第三天及以后的天數(shù)都不會(huì)統(tǒng)計(jì)該員工的缺勤計(jì)分
----這些天也不算為缺勤。
---這個(gè)怎么實(shí)現(xiàn)呢？
---最簡(jiǎn)單的方法，就是允許severityPoint允許為0
---更新 ‘長(zhǎng)病假'，同時(shí)缺勤計(jì)分為0
update absence set severityPoints=0,reason='長(zhǎng)病假'
where exists (select * from absence as a where absence.empId=a.empId
and absence.absenceDate=dateadd(d,-1,a.absenceDate)

)

--1.如果員工在一年內(nèi)的缺勤計(jì)分達(dá)到50，就會(huì)可以解雇該員工。
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)
--
---上面的語(yǔ)句中是否存在Bug呢？
---有。
---1.子查詢沒(méi)有按鈕要求檢查員工的缺勤計(jì)分在一年內(nèi)是否達(dá)到或超過(guò)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)于在刪除了員工之后，卻沒(méi)有刪除此員工的缺勤記錄，需要顯式刪除或隱式刪除，隱式刪除可以添加級(jí)聯(lián)刪除。

---假設(shè)：如果在缺勤期間，員工不會(huì)調(diào)換部門(mén)，則可以使用以下語(yǔ)句來(lái)提高性能
update absence set severityPoints=0,reason='長(zhǎng)病假'
where exists (select * from absence a where a.empId=absence.empId and
dateadd(d,-1,absence.absenceDate)=a.absencedate
)

-------第三，再考慮以下這種情況：
---對(duì)于跨星期的長(zhǎng)病假，例如：如果你在周未生病，對(duì)公司來(lái)說(shuō)是沒(méi)有問(wèn)題的，因?yàn)橹芪匆话闶遣挥蒙习嗟摹?
---接著你在第二個(gè)星期一個(gè)星期都缺勤了，第三個(gè)星期的星期一也缺勤了，上面的update語(yǔ)句只會(huì)把第二個(gè)星期的五個(gè)工作日
----設(shè)為長(zhǎng)病假，第一周的周五與第三周的周一，都將視為缺勤，并計(jì)算缺勤計(jì)分。所以上面的update中的子查詢需要對(duì)
---遺漏的一系列日期做額外的處理。
---方法：
---1.對(duì)預(yù)先安排好的休息日（如周六，周日，五一，十一等）添加一個(gè)缺勤計(jì)分為0的代碼，可以避免周未與節(jié)假日的問(wèn)題。
--對(duì)于這類(lèi)問(wèn)題需要新增一張表calendar用來(lái)計(jì)算員工的工作 。
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