Dunno if this is the fanciest way to do this, but here's a quick answer:
DECLARE @Employee TABLE (EmployeeID INT, EmployeeName VARCHAR (100), EmployeeDOB DATETIME)
INSERT INTO @Employee
SELECT 101, 'James Bond', '07/07/1945' UNION ALL
SELECT 102, 'Tanned Tarzan', '12/13/1955' UNION ALL
SELECT 103, 'Dracula Transylvanian', '10/22/1967'
DECLARE @EmployeeDesignation TABLE (EmployeeID INT, Designation VARCHAR (100), EffectiveDate DATETIME)
INSERT INTO @EmployeeDesignation
SELECT 101, 'Bond Intern', '01/01/1970' UNION ALL
SELECT 101, 'Bond Trainee', '01/01/1975' UNION ALL
SELECT 101, 'Bond...James Bond', '01/01/1985' UNION ALL
SELECT 102, 'Baby in Animal Care', '01/01/1956' UNION ALL
SELECT 102, 'Survivor', '07/01/1966' UNION ALL
SELECT 102, 'King of the Jungle', '11/12/1975' UNION ALL
SELECT 103, 'Blood Drawing Trainee', '4/18/1985' UNION ALL
SELECT 103, 'Eternal Blood Sucker', '10/31/1990'
DECLARE @EmployeeSalaryHistory TABLE (EmployeeID INT, Salary MONEY, EffectiveDate DATETIME)
INSERT INTO @EmployeeSalaryHistory
SELECT 101, 5000.00, '01/01/1970' UNION ALL
SELECT 101, 25000.00, '11/21/1979' UNION ALL
SELECT 101, 49000.00, '6/01/1985' UNION ALL
SELECT 102, 22000.00, '01/01/1956' UNION ALL
SELECT 102, 87450.00, '09/15/1965' UNION ALL
SELECT 103, 14600.00, '4/18/1985' UNION ALL
SELECT 103, 23600.00, '09/01/1989' UNION ALL
SELECT 103, 143300.00, '2/21/1999'
;WITH StartingDesignation AS
(
SELECT
Designation,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate)
FROM @EmployeeDesignation
),
CurrentDesignation AS
(
SELECT
Designation,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate DESC)
FROM @EmployeeDesignation
),
StartingSalary AS
(
SELECT
Salary,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate)
FROM @EmployeeSalaryHistory
),
CurrentSalary AS
(
SELECT
Salary,
EmployeeID,
RN = ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY EffectiveDate DESC)
FROM @EmployeeSalaryHistory
)
SELECT
E.EmployeeID,
E.EmployeeName,
E.EmployeeDOB,
SD.Designation AS EmployeeStartingDesignation,
CD.Designation AS EmployeeCurrentDesignation,
SS.Salary AS EmployeeStartingSalary,
CS.Salary AS EmployeeCurrentSalary
FROM @Employee AS E
INNER JOIN StartingDesignation AS SD ON
E.EmployeeID = SD.EmployeeID AND
SD.RN = 1
INNER JOIN CurrentDesignation AS CD ON
E.EmployeeID = CD.EmployeeID AND
CD.RN = 1
INNER JOIN StartingSalary AS SS ON
E.EmployeeID = SS.EmployeeID AND
SS.RN = 1
INNER JOIN CurrentSalary AS CS ON
E.EmployeeID = CS.EmployeeID AND
CS.RN = 1