Puzzle 2 absentee

I really see this puzzle .... A huge expense. But I finally learned something.

The puzzle is described as: There is a database that records absenteeism. When the Severity Score reaches 40, the system is automatically dismissed. When an employee is absent for more than one day, the employee is considered to be a long vacation, and the employee's severity score is not counted for the second and third days.

Create personnel <br/> (<br/> emp_id integer primary key <br/> ); <br/> Create Table absenteeism <br/> (<br/> emp_id integer not null references personnel (emp_id) on Delete cascade, <br/> absent_date date not null, <br/> reason_code char (40) not null, <br/> severity_points integer not null check (severity_points between 0 and 4) <br/> );

When a personnel is automatically fired, the corresponding records of absenteeism will be deleted, so we use cascading deletion technology.

 

1. How to manage long holidays.

The trigger is used here. For details about the trigger, see my other article.

When a tuple is inserted into absenteeism, each record is analyzed. If the time of two records is consecutive, the severity points of the last time are changed to 0.

Create trigger name on absenteeism <br/> for insert <br/> as <br/> If Update (absent_date) <br/> begin <br/> Update absenteeism <br/> set severity_points = 0, <br/> reason_code = 'Long Term Illness '<br/> where exists <br/> (select * <br/> from absenteeism as A2 <br/> where datediff (dd, a2.absent _ date, absenteeism. absent_date) = 1 <br/> and absenteeism. emp_id = a2.emp _ id) <br/> end <br/>

2. automatic dismissal

I asked csdn experts about this question ~ The answer they gave me was to use an SQL proxy. This can be achieved by regularly detecting given SQL statements. You can also use a trigger to trigger this statement when a tuples are inserted into absenteeism.

Bytes ------------------------------------------------------------------------------------------------------------------

Management Studio
-- SQL Server proxy
-- Right-click a job
-- Create a job
-- Enter the job name in "general"
-- "Step"
-- New
-- Enter the step name in "Step name"
-- Select "Transact-SQL script (tsql)" in "type )"
-- "Database": select the database for Command Execution
-- Enter the statement to be executed in "command:
The SQL statement you want to execute
-- OK
-- "Plan" item
-- Create a scheduler
-- Enter the scheduler name in "name"
-- Select your job execution schedule in "plan type"
-- If "repeated appears" is selected"
-- Click "change" to set your schedule
Then start the SQL Agent service and set it to Automatic startup. Otherwise, your job will not be executed.

Setting method:
My computer -- control panel -- Administrative Tools -- service -- Right-click SQLServerAgent -- properties -- start type -- select "auto start" -- OK.
Bytes ----------------------------------------------------------------------------------------------------------------------

Delete from personnel <br/> where emp_id = (<br/> select a1.emp _ id <br/> from absenteeism as A1 <br/> where a1.emp _ id = personnel. emp_id <br/> and absent_date <br/> between dateadd (dy,-365, getdate () <br/> and getdate () <br/> group by a1.emp _ id <br/> having sum (severity_points) >=40 <br/> );

The following table lists the expired records that are automatically deleted for more than one year.

Delete from absenteeism <br/> where absent_date between dateadd (dy,-365, getdate () <br/> and getdate ();

 

Summary:

1. On Delete Cascade

2. Trigger

3. SQLAgent