JobNumber varchar(6), CostCode varchar(6), Status char(1), StartDate DateTime, EndDate DateTime

);

insert into test values

('566169','053800','D','2021-10-08 00:00:00.000','2021-10-18 14:01:55.517'),

('566170','010800','D','2022-05-18 00:00:00.000','2022-05-22 15:38:58.447'),

('566170','010800','B','2022-04-04 00:00:00.000',NULL),

('566170','026200','B','2022-04-21 00:00:00.000',NULL),

('566170','026200','A','2022-05-06 00:00:00.000',NULL),

('566175','033500','A',NULL,NULL);

FROM (

SELECT JobNumber, CostCode, Status, StartDate, EndDate

, row_number() over

(

partition by JobNumber, CostCode

order by case when status='D' then 0 else 1 end, StartDate, EndDate

) rn

FROM [test]

) t

WHERE rn = 1