SQL Query Inactive Users with last end date

This is a follow up from a question I asked about a year ago Old thread The answers I got then have worked fine but now I discovered that I need to tweak the query to be able to get the latest end date for the inactive users.

So again here’s a quick example table of users, some are active and some are inactive and some have several period of employment.

when someone is reemployed a new row will be added for that employment period. Username will always be the same.

So I want to find which users that is disabled and doesn’t have an active employment also if there is several period of employment I want the one that has the latest end date. One row per username with all the columns.

The database is SQL Server 2016.

Example table:

|  username | name       | active | Job title   | enddate 
+-----------+----------- +--------+-------------+----------
| 1111      | Jane Doe   |    1   | CIO         | 1/3/2022
| 1111      | Jane Doe   |    0   | Janitor     | 1/2/2018
| 1112      | Bob Doe    |    1   | Coder       | NULL
| 1113      | James Doe  |    0   | Coder       | 1/3/2018
| 1114      | Ray Doe    |    1   | Manager     | NULL
| 1114      | Ray Doe    |    0   | Clerk       | 2/2/2019
| 1115      | Emma Doe   |    1   | Waiter      | NULL
| 1116      | Sarah Doe  |    0   | Greeter     | 3/4/2016
| 1116      | Sarah Doe  |    0   | Trainer     | 4/5/2019

So for user 1116 I would ideally get one row with enddate 4/5/2019

The query I use from the answers in the old thread is this one:

    ;WITH NonActiveDisabledUsers AS
(
    SELECT DISTINCT
        U.username
    FROM
        UserEmployment AS U
    WHERE
        U.active = 0 AND
        NOT EXISTS (SELECT 'no current active employment' 
                    FROM UserEmployment AS C
                    WHERE U.username = C.username AND 
                    C.active = 1 AND
                    (C.enddate IS NULL OR C.enddate >= CONVERT(DATE, GETDATE())))
)
SELECT
    R.*
FROM
    NonActiveDisabledUsers AS N
    CROSS APPLY (
        SELECT TOP 1        -- Just 1 record
            U.*
        FROM
            UserEmployment AS U
        WHERE
            N.username = U.username AND
            U.active = 0
        ORDER BY
            U.enddate DESC  -- Determine which record should we display
        ) AS R

This gives me the right user and employment status but not the latest end date since it will get the first result for user 1116

Answer

We can use conditional aggregation with a window aggregate to get the number of active rows for this user.

We then filter to only inactive, and row-number the result by enddate taking the first row per group:

SELECT
    username,
    name,
    active,
    [Job title],
    enddate 
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY username ORDER BY enddate DESC)
    FROM (
        SELECT *,
          CountOfActive = COUNT(CASE WHEN
                Active = 1 AND
                (enddate IS NULL OR enddate >= CONVERT(DATE, GETDATE())) THEN 1 END
              ) OVER (PARTITION BY username)
        FROM UserEmployment
    ) AS t
    WHERE CountOfActive = 0
) AS t
WHERE rn = 1;

Note that the row-numbering does not take into account nulls in enddate which would be sorted last. You would need a conditional ordering:

ROW_NUMBER() OVER (PARTITION BY username ORDER BY CASE WHEN enddate IS NULL THEN 0 ELSE 1 END ASC, enddate DESC)