Here are 2 more options that you could test. I'm not sure if they're any better, but at least they read the tables just once. The second one should be faster as it's sorting on (theoretically) less rows.
WITH CTE AS(
SELECT e.EmployeeID,
e.EmployeeName,
e.EmployeeDOB,
ed.Designation,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY ed.EffectiveDate ASC) DesignationNoASC,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY ed.EffectiveDate DESC) DesignationNoDESC,
es.Salary,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY es.EffectiveDate ASC) SalaryNoASC,
ROW_NUMBER() OVER( PARTITION BY e.EmployeeID ORDER BY es.EffectiveDate DESC) SalaryNoDESC
FROM @Employee e
JOIN @EmployeeDesignation ed ON e.EmployeeID = ed.EmployeeID
JOIN @EmployeeSalaryHistory es ON e.EmployeeID = es.EmployeeID
)
SELECT EmployeeID,
EmployeeName,
EmployeeDOB,
MAX( CASE WHEN DesignationNoASC = 1 THEN Designation END) EmployeeStartingDesignation,
MAX( CASE WHEN DesignationNoDESC = 1 THEN Designation END) EmployeeCurrentDesignation,
MAX( CASE WHEN SalaryNoASC = 1 THEN Salary END) EmployeeStartingSalary,
MAX( CASE WHEN SalaryNoDESC = 1 THEN Salary END) EmployeeCurrentSalary
FROM CTE
GROUP BY EmployeeID,
EmployeeName,
EmployeeDOB;
WITH Designations AS(
SELECT EmployeeID,
Designation,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate ASC) DesignationNoASC,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) DesignationNoDESC
FROM @EmployeeDesignation
),
Salaries AS(
SELECT EmployeeID,
Salary,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate ASC) SalaryNoASC,
ROW_NUMBER() OVER( PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) SalaryNoDESC
FROM @EmployeeSalaryHistory
)
SELECT e.EmployeeID,
e.EmployeeName,
e.EmployeeDOB,
MAX( CASE WHEN DesignationNoASC = 1 THEN Designation END) EmployeeStartingDesignation,
MAX( CASE WHEN DesignationNoDESC = 1 THEN Designation END) EmployeeCurrentDesignation,
MAX( CASE WHEN SalaryNoASC = 1 THEN Salary END) EmployeeStartingSalary,
MAX( CASE WHEN SalaryNoDESC = 1 THEN Salary END) EmployeeCurrentSalary
FROM @Employee e
JOIN Designations d ON e.EmployeeID = d.EmployeeID
JOIN Salaries s ON e.EmployeeID = s.EmployeeID
WHERE (d.DesignationNoASC = 1 OR d.DesignationNoDESC = 1)
AND (s.SalaryNoASC = 1 OR s.SalaryNoDESC = 1)
GROUP BY e.EmployeeID,
e.EmployeeName,
e.EmployeeDOB;