t, Sql Server - rextester

Language: Layout:

declare @TempTable table (ClaimNumber varchar(50), ActivityID int, Activity varchar(50), ActivityDate datetime, ClaimStatus int)
insert into @TempTable values   ('Claim1', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0 ),
                                ('Claim1', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0 ),
                                ('Claim1', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0 ),
                                ('Claim1', 8, 'ClaimClosed', '2018-11-01 10:53:00.087', 1 ),
                                ('Claim2', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0 ),
                                ('Claim2', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0 ),
                                ('Claim2', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0 ),
                                ('Claim3', 0, 'ClaimCreated', '2018-05-04 10:58:56.510', 0 ),
                                ('Claim3', 4, 'ReserveCreated', '2018-05-09 09:52:52.327', 0 ),
                                ('Claim3', 6, 'PaymentCreated', '2018-05-15 13:17:47.920', 0 ),
                                ('Claim3', 8, 'ClaimClosed', '2018-11-01 10:53:00.087', 1 )
                                
                                
                               
WITH cte as (
    SELECT ClaimNumber, 
           ClaimStatus,
           ROW_NUMBER() over (PARTITION BY ClaimNumber ORDER BY ActivityDate DESC) as rn
    FROM @TempTable
)
SELECT t.*
FROM @TempTable t
JOIN (SELECT *
      FROM cte
      WHERE rn = 1) f
  ON t.ClaimNumber = f.ClaimNumber
 AND f.ClaimStatus = 0

View schema
Execution time: 0,02 sec, rows selected: 14, rows affected: 11, absolute service time: 0,22 sec, absolute service time: 0,21 sec