ADVANCED DATABASE QUESTION

I have these tables in sql database.

SOLVE:

Create a SQL Server Trigger called called calcOvertime which fires before an update is made on the rosterEvent table. When it fires, it should calculate the amount of overtime in hours the employee has worked for that shift. Overtime is calculated on the basis of the amount of hours after the appointed end of the shift they are assigned to work on. No overtime is calculated for employees clocking in early. So an employee clocking at 09:55 and clocking out at 12:15 for a shift from 10am-12pm will get 0.25 hours overtime. Given this, overtime is stored as a decimal.

If the employee has clocked in late for their allocated shift, the overtime hours should be set to zero regardless of how many minutes passed their allocated shift end that they work.

mysql> DESCRIBE Employee; | Field 1 Type Null i Key | Default | Extra +---- I id | int | NO | PRI NULL | auto_increment | FirstName | varchar(30) | YES | NULL | SurName | varchar(30) | YES | NULL | DateOfBirth | date | YES I | NULL | MobileNumber | varchar(30) | YES | NULL 5 rows in set (0.07 sec) mysql> DESCRIBE Shift; +---- | Field | Type Null | Key | Default | Extra += I id I int | NO | PRI I NULL | auto_increment | | DayType | varchar(30) | YES I | NULL | TimePeriod | varchar(30) | YES | | NULL | StartTime I time | YES I | NULL | EndTime | time | YESI | NULL 5 rows in set (0.00 sec) | Type mysql> DESCRIBE RosterEvent; +-- | Field Null | Key | Default | Extra +== +------+-----+ I id I int | NO | PRI NULL | auto_increment | | employeeID I int | YES | NULL | shiftID | int | YESI | NULL | rosterEventDate | date | YES | NULL I clockInTime | time | YES | NULL | clockOutTime I time | YES I | NULL | overtime I tinyint(1) | YES I | NULL +----- 7 rows in set (0.01 sec)