SQL statement exercise Example 6 absence (vacation) statistics in the Personnel System

Copy codeThe Code is as follows:
--- This is an example in the human affairs system. It is required to record the employee's absence status.
--- 1. Recording the absence score in the table is a penalty score for those who are often absent.
--- Rules:
--- 1. If the employee's absence score within one year reaches 50, the employee can be dismissed.
--- 2. If an employee is absent for more than one consecutive day, the employee is considered to have a long sick leave. At this time, the employee's absence score will not be counted on the next day, the third day, or the next day.
---- These days are not counted as absenteeism.

Create table absence
(
EmpId int not null,
AbsenceDate datetime not null,
Reason nvarchar (100) not null,
SeverityPoints int

)
Go
---- 2. If an employee is absent for more than one consecutive day, the employee is considered to have a long sick leave. At this time, the employee's absence score will not be counted on the next day, the third day, or the subsequent days.
---- These days are not counted as absenteeism.
--- How can this be achieved?
--- The simplest method is to allow severityPoint to be 0
--- Update long sick leave and score 0 for absence
Update absence set severityPoints = 0, reason = 'long sick off'
Where exists (select * from absence as a where absence. empId = a. empId
And absence. absenceDate = dateadd (d,-1, a. absenceDate)

)

-- 1. If the employee's absence score within one year reaches 50, the employee can be dismissed.
Select empid, SUM (severityPoints) as totalPoints
From absence
Group by empId

-- Delete
-- Delete from employee where empid = (select a. empid from absence where a. empid = employee. empid group by a. empid having sum (severityPoints)> = 50)
--
--- Is there a Bug in the above statement?
--- Yes.
--- 1. The subquery does not have a button to check whether the employee's absence score reaches or exceeds 50 within one year. It needs to check the additional date range in the where subname.
-- Delete after modification
-- 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 if the employee's absence record is not deleted after the employee is deleted, the employee's absence record needs to be deleted explicitly or implicitly. If the employee is deleted implicitly, cascade deletion can be added.

--- Hypothesis: If employees do not change their Departments during absence, you can use the following statement to improve performance:
Update absence set severityPoints = 0, reason = 'long sick off'
Where exists (select * from absence a where a. empId = absence. empId and
Dateadd (d,-1, absence. absenceDate) = a. absencedate
)

------- Third, consider the following situations:
--- For cross-week long sick leave, for example, if you are not ill in the week, it is no problem for the company, because weekly does not usually need to go to work.
--- Then you will be absent from work in the second Monday week, and absent in the third Monday. The above update statement will only set the five working days of the second week
---- For long sick leave, Friday of the first week and Monday of the third week are considered as absenteeism and the score for absenteeism is calculated. Therefore, the subquery in the above update needs
--- Handle a series of dates that are missing.
--- Method:
--- 1. Add a code with the absence score of 0 to the pre-arranged rest days (such as Saturday, Sunday, May Day, and 11) to avoid the issue of weekly absence and holidays.
-- For this type of problem, you need to add a table calendar to calculate the work of employees.
Create table calendar
(
CalendarDate datetime, -- Date
Weeks int, -- week
Weekday varchar (20), -- day of the week
)


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