Duplicate session Records - Ending all but latest record

Question

I am trying to identify duplicate sessions in a log table on sql server.

A user should only ever have one open login session at a time however sometimes when the app crashes, it leaves user sessions open causing issues.

I want to find all users with multiple open sessions (end date would be null) and then put a end date on all but the latest open session.

Example table columns would be Username, start_time, end_time

Example data

henrya  2023-03-30 11:15:00.493 NULL
henrya  2023-03-30 11:00:00.493 NULL
henrya  2023-03-30 10:15:00.493 10:30:00.493
qwertya  2023-03-30 12:15:00.493 NULL
qwertya  2023-03-30 12:00:00.493 NULL
qwertya  2023-03-30 10:15:00.493 NULL

I am having difficulty with the update script to end all but the latest session per user.

Answer 1

To find all extra rows you can use ROW_NUMBER. You can use an updatable CTE or subquery for this, and do the update directly on that without re-joining

WITH cte AS (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY Username ORDER BY start_time DESC)
    FROM YourTable
    WHERE end_time IS NULL
)
UPDATE cte
SET end_time = SYSUTCDATETIME()
WHERE rn > 1;