Getting the latest test per device is a typical top-1-per-group problem, which we can approach with row_number() and filtering:

select *
from (
    select r.*,
        row_number() over(partition by DVTR_DeviceNo order by DVTR_TestedOnAt desc) rn
    from DvTestResults r
) r
where rn = 1

We would then left join on the testers table to try and bring the tester’s name.

Preferably, like with DV00001 in this example, the tester is typically the same name of the last person who tested it (in this case, John)

Retrieving the latest tester (hence ignoring null values) is a more complex task in SQL Server. We can either use apply , or more window functions. The latter would be:

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

Related: How to make LAG() ignore NULL s in SQL Server

Demo, based on the test data created by @marcothesane:
| 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 |

Based on my new understanding of the problem, this should work:

SELECT DVTR_DeviceNo, MAX(DVTR_TestedOnAt) As DVTR_TestedOnAt
    , coalesce(
        (
         SELECT T_TestName
         FROM (
            SELECT DVTR_TesterNo, DVTR_DeviceNo 
               , row_number() over 
                   (PARTITION BY DVTR_DeviceNo
                    ORDER BY case when DVTR_TesterNo IS NULL THEN 1 ELSE 0 END
                               ,DVTR_TestedOnAt DESC) rn     
            FROM DvTestResults
         ) dtr0
         LEFT JOIN Tester t ON t.T_TesterNo = dtr0.DVTR_TesterNo
         WHERE dtr0.rn = 1 AND dtr0.DVTR_DeviceNo = dtr.DVTR_DeviceNo
        ) 
      , 'Anonymous') T_TestName
FROM DvTestRestuls dtr
GROUP BY DVTR_DeviceNo

I would work with MAX(dvtr_testerno) OVER(PARTITION BY dvtr_deviceno) , in SQL Server, to compensate for the missing LAST_VALUE(a IGNORE NULLS) to backfill the missing foreign keys where I can, in a subquery on DVTestResults (I call that subquery w_testerno ), and then left join that subquery with the testers; the resulting NULLs for t_testername become 'Anonymous' thanks to an IFNULL() :

WITH
-- your input
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'
)
-- end of your input. Query starts here, replace following comma with "WITH"
,
w_testerno AS (
  SELECT
    dvtr_deviceno
  , dvtr_testedonat
  , MAX(dvtr_testerno) OVER(
      PARTITION BY dvtr_deviceno 
    ) AS dvtr_testerno
  FROM dvtestresults
)
SELECT 
  dvtr_deviceno
, dvtr_testedonat
, isnull(t_testername,'Anonymous') AS t_testermane
FROM      w_testerno
LEFT JOIN tester ON t_testerno = dvtr_testerno
ORDER BY 1,2;