I have a table that stores employee information. This table is used by an external service to determine employee rates per hour. Each employee has a column attribute named EmployeeGroup. Everyone in a specific employee group gets the same rate. A change in EmployeeGroup means that the employee’s rate should change as well. I need to extract only the latest change to the employee’s group, which means we disregard any previous changes. If there are no changes, we also need to return the first instance of the group they belong to. Here is a crude example of the table I am working with (Not all columns present):

As you may observe, John Smith (EmployeeId:123) underwent a number of changes. We disregard any previous changes and we only worry about his current group (Seniors) and when that happened on 2023-06-20 (5th row). Any subsequent rows that aren’t a change in employee group are irrelevant.

I also need to return the ‘default’ StartDate if there isn’t any change. e.g.) Brody James (EmployeeId 789) has remained a Mid-level even though there was a position change. Thus we only need the first date which was when he was hired (2018-01-01)

I created the following query, which I thought to retrieve the previous changes via LAG(). I was hoping to retrieve the last changed query and this would then give an indication of what’s changed and what hasn’t. But I can’t for the life of me figure out how to extract only the latest and also the first of those that haven’t changed their employee group.

WITH PreviousEmployeeChanges AS (
    SELECT
       EmployeeId,
       LAG(EmployeeGroup) OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey) PrevSubgroup,
       LAG(StartDate) OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey) AS PrevStartDate,
       ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY EmployeeSKey desc) AS rn --Even thought to use this to somehow sort by latest change
    FROM
        Employees
)
--tried combining the CTE and original to perhaps give me my desired output
SELECT e.*, pec.PrevSubgroup, pec.PrevStartDate,
COALESCE(pec.PrevStartDate, e.startDate) AS LastChangedEmployeeGroup
FROM
    Employees e
JOIN
    PreviousEmployeeChanges pec ON e.employeeId = pec.employeeId 

I have not used a GROUP BY because there are about 30 other columns that also need to be returned. How can I correctly return the employees with only the latest change in employee group while accounting for those that never got changed? I am at my wits end on how to bring these two parts together.

Here is the desired result
| EmployeeId | FirstName| LastName | Position| startDate | EmployeeGroup | EmployeeSKey | ActionDesc |
| ——–| ——– | ——– |——– | ——– | ——– | ——– | ——– | ——– |
| 123 | John | Smith |Senior Dev | 2023-06-20| Seniors | 9 | Promotion|
| 456 | Sally | Jones |Jnr Researcher | 2022-01-07| Junior | 6 | Hired|
| 789 | Brody | James |N Wing Janitor | 2018-01-01| Mid-Levels | 3 | Hired|
| 101 | Paul | Gaib |Senior VP | 2024-01-01| Snr Executives | 10 | Nepotism|

Here is the insert statement to replicate the above table if needed

INSERT INTO Employees (UserId, FirstName, LastName, Position, StartDate, EmployeeGroup, EmployeeSKey, ActionDescription)
VALUES
    (123, 'John', 'Smith', 'Intern', '2016-01-01', 'Interns', 1, 'Hired'),
    (123, 'John', 'Smith', 'Junior Dev', '2018-01-01', 'Juniors', 2, 'Absorbed'),
    (123, 'John', 'Smith', 'Junior Dev', '2019-07-15', 'Juniors', 3, 'Team change'),
    (123, 'John', 'Smith', 'Intermediate Dev', '2021-01-06', 'Intermediates', 4, 'Promotion'),
    (123, 'John', 'Smith', 'Senior Dev', '2023-06-20', 'Seniors', 5, 'Promotion'),
    (123, 'John', 'Smith', 'Senior Dev', '2024-01-24', 'Seniors', 6, 'Manager change'),
    (456, 'Sally', 'Jones', 'Junior Researcher', '2022-01-07', 'Juniors', 7, 'Hired'),
    (789, 'Brody', 'James', 'Janitor', '2018-01-02', 'Intermediates', 8, 'Hired'),
    (789, 'Brody', 'James', 'Janitor', '2022-01-09', 'Intermediates', 9, 'Manager change'),
    (101, 'Paul', 'Gaib', 'Junior VP', '2023-06-01', 'Jnr Executives', 10, 'Nepotism'),
    (101, 'Paul', 'Gaib', 'Senior VP', '2024-01-01', 'Snr Executives', 11, 'Nepotism');