By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tableName
(
JobNumber varchar(300),
CostCode varchar(300),
Status varchar(300),
StartDate varchar(300),
EndDate varchar(300)
);
INSERT INTO tableName (JobNumber,CostCode,Status,StartDate,EndDate )
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');
6 rows affected
SELECT
jobNumber,
costCode,
max(startdate) as startdate,
max(enddate) as enddate
from tablename
where status = 'D'
group by jobNumber, costcode
UNION
select
jobnumber,
costcode,
min(startdate) as startdate,
min(enddate) as enddate
from tablename
where CONCAT(jobNumber,':',costCode) not in (SELECT CONCAT(jobNumber,':',costCode) from tablename where status='D')
group by jobnumber, costcode
| jobNumber | costCode | startdate | enddate |
|---|---|---|---|
| 566169 | 053800 | 2021-10-08 00:00:00.000 | 2021-10-18 14:01:55.517 |
| 566170 | 010800 | 2022-05-18 00:00:00.000 | 2022-05-22 15:38:58.447 |
| 566170 | 026200 | 2022-04-21 00:00:00.000 | NULL |
| 566175 | 033500 | NULL | NULL |