LAG function unable to capture the previous record information when used in join

Question

I have two tables:

Employee:

Employee	Phone Num	StartDate	EndDate	Dept	DeptName
50	5478765	12-Jan-23	29-Jan-2023	5	Physics
50	8756544	29-Jan-23	31-Dec-9999	6	Chemistry

EmployeeSalary:

Employee	Salary	StartDate	EndDate	Dept
50	5000000	12-Jan-23	31-Dec-9999	6

I want to join these two tables, from EmployeeSalary table I need the salary information whereas from Employee table, I need the last phone number i.e. the phone number which was entered in the period 12-Jan-23 and 29-Jan-2023 Phone number --> 5478765 and the current department name.

I am joining these two tables and using LAG function to extract the previous phone number. But when this is joined using EmployeeID and DepartmentNumber it is not showing the previous phone number.

I need output like this:

Employee	Salary	StartDate	EndDate	Dept	PhoneNumber	DepartmentName
50	5000000	12-Jan-23	31-Dec-9999	5	5478765	Chemistry

Answer 1

SELECT es.Employee,
       Salary,
       es.StartDate,
       es.EndDate,
       prevDept,
       prevPhone,
       DeptName
FROM EmployeeSalary AS es
    INNER JOIN
    (
        SELECT Employee,
               Dept,
               DeptName,
               LAG(PhoneNum) OVER (PARTITION BY Employee ORDER BY StartDate) prevPhone,
               LAG(Dept) OVER (PARTITION BY Employee ORDER BY StartDate) prevDept
        FROM employee
    ) AS e
        ON e.Employee = es.Employee
           AND e.Dept = es.Dept;

Employee	Salary	StartDate	EndDate	prevDept	prevPhone	DeptName
50	5000000	2023-01-12	9999-12-31	5	5478765	Chemistry

DBFiddle demo