This is for MySQL adventure works 2017 database
Use a temporary table to save the DepartmentID and the highest pay rate of each employee in that department. This will use a subquery. Use the table HumanResources.EmployeePayHistory. Minimize the rows that will be added to the table by using DISTINCT. Make sure all columns in the temp table have a name.
Table Human Resources.EmployeePay History (316 rows) Employee pay history. Nullable Default PK, FK PK Column Data Type Identity BusinessEntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. RateChangeDate Rectangular Ship datetime Date the change in pay is effective Rate money Salary hourly rate. PayFrequency tinyint 1 = Salary received monthly, 2 = Salary received biweekly ModifiedDate datetime Date and time the record was last updated. getdate() Table Human Resources. EmployeeDepartment History. (296 rows) Employee department transfers. Nullable Default Column Data Type Identity PK, FK Business EntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. PK, FK DepartmentID smallint Department in which the employee worked including currently. Foreign key to Department. DepartmentID. PK, FK ShiftID tinyint Identifies which 8-hour shift the employee works. Foreign key to Shift. Shift.ID. PK StartDate date Date the employee started work in the department. EndDate date Date the employee left the department. NULL = Current department. ModifiedDate datetime Date and time the record was last updated. X getdate() Table Human Resources.EmployeePay History (316 rows) Employee pay history. Nullable Default PK, FK PK Column Data Type Identity BusinessEntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. RateChangeDate Rectangular Ship datetime Date the change in pay is effective Rate money Salary hourly rate. PayFrequency tinyint 1 = Salary received monthly, 2 = Salary received biweekly ModifiedDate datetime Date and time the record was last updated. getdate() Table Human Resources. EmployeeDepartment History. (296 rows) Employee department transfers. Nullable Default Column Data Type Identity PK, FK Business EntityID int Employee identification number. Foreign key to Employee. BusinessEntityID. PK, FK DepartmentID smallint Department in which the employee worked including currently. Foreign key to Department. DepartmentID. PK, FK ShiftID tinyint Identifies which 8-hour shift the employee works. Foreign key to Shift. Shift.ID. PK StartDate date Date the employee started work in the department. EndDate date Date the employee left the department. NULL = Current department. ModifiedDate datetime Date and time the record was last updated. X getdate()
