SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

(No column name)
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

WITH
dvtestresults(dvtr_deviceno,dvtr_testedonat,dvtr_testerno) AS (
          SELECT 'DV00001','2022-08-11 14:15:16.000',0001
UNION ALL SELECT 'DV00001','2022-08-19 21:08:16.000',NULL
UNION ALL SELECT 'DV00001','2022-09-22 08:14:32.000',NULL
UNION ALL SELECT 'DV00002','2023-06-03 18:18:03.000',NULL
UNION ALL SELECT 'DV00002','2023-08-15 19:01:36.000',0007
UNION ALL SELECT 'DV00003','2022-12-23 08:04:47.000',0014
UNION ALL SELECT 'DV00003','2023-01-03 10:09:51.000',0014
UNION ALL SELECT 'DV00003','2023-01-09 08:01:33.000',0014
UNION ALL SELECT 'DV00004','2023-03-14 11:49:02.000',0298
UNION ALL SELECT 'DV00004','2023-03-15 09:08:13.000',0298
UNION ALL SELECT 'DV00005','2022-04-28 16:23:14.000',NULL
UNION ALL SELECT 'DV00005','2022-08-14 08:20:56.000',NULL
)
,
tester(t_testerno,t_testername) aS (
          SELECT 0001,'John'
UNION ALL SELECT 0007,'Stacy'
UNION ALL SELECT 0014,'James'
UNION ALL SELECT 0298,'Carlos'
)
select r.DVTR_DeviceNo, r.DVTR_TestedOnAt, coalesce(t.T_TesterName, 'Anonymous') T_TesterName
from (
    select r.*,
        max(DVTR_TesterNo) over(partition by DVTR_DeviceNo, grp) LastDVTR_TesterNo
    from (
        select r.*,
            row_number()         over(partition by DVTR_DeviceNo order by DVTR_TestedOnAt desc) rn,
            count(DVTR_TesterNo) over(partition by DVTR_DeviceNo order by DVTR_TestedOnAt) grp
        from DvTestResults r
    ) r
) r
left join Tester t on t.T_TesterNo = r.LastDVTR_TesterNo
where r.rn = 1

DVTR_DeviceNo	DVTR_TestedOnAt	T_TesterName
DV00001	2022-09-22 08:14:32.000	John
DV00002	2023-08-15 19:01:36.000	Stacy
DV00003	2023-01-09 08:01:33.000	James
DV00004	2023-03-15 09:08:13.000	Carlos
DV00005	2022-08-14 08:20:56.000	Anonymous

Warning: Null value is eliminated by an aggregate or other SET operation.