SQL Server 2019

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

CREATE TABLE DevInfo (
  Center CHAR(2),
  DevId INT,
  DevType CHAR(2),
  LatestDate DATE,
  DEL BIT
)
CREATE TABLE CenInfo (
  Center CHAR(2),
  LatestDate DATE
)
CREATE TABLE TypeInfo (
  DevType CHAR(2),
  LatestDate DATE
)
INSERT INTO DevInfo (Center, DevId, DevType, LatestDate, DEL) VALUES 
('C1', 111, 'T1', '01/06/2020', 0),
('C1', 222, 'T2', '01/06/2020', 0),
('C2', 111, 'T3', '01/06/2020', 0),
('C2', 333, 'T4', '01/06/2020', 0),
('C3', 222, 'T5', '03/06/2020', 0),
('C4', 444, 'T6', '01/06/2020', 0)
6 rows affected

INSERT INTO CenInfo (Center, LatestDate) VALUES
('C1', '01/06/2020'),
('C1', '01/06/2020'),
('C2', '01/06/2020'),
('C2', '01/06/2020'),
('C3', '03/06/2020'),
('C4', '01/06/2020')
6 rows affected

INSERT INTO TypeInfo (DevType, LatestDate) VALUES
('T1', '01/06/2020'),
('T2', '01/06/2020'),
('T3', '01/06/2020'),
('T4', '01/06/2020'),
('T5', '03/06/2020'),
('T6', '01/06/2020')
6 rows affected

WITH DevInfoFilteredAndSorted AS (
  SELECT 
    DI.Center, 
    DI.DevId, 
    DI.DevType, 
    DI.LatestDate,
    ROW_NUMBER() OVER (PARTITION BY DI.DevId ORDER BY CASE WHEN DI.DevType = 'T3' THEN 1 ELSE 0 END, DI.LatestDate DESC) AS RN
  FROM DevInfo AS DI
  INNER JOIN CenInfo AS CI
    ON DI.Center = CI.Center
  INNER JOIN TypeInfo AS TI
    ON DI.DevType = TI.DevType
  WHERE DI.DEL = 0
)

SELECT DI.Center, DI.DevId AS "Dev id", DI.DevType AS "Dev Type", DI.LatestDate AS "Latest Date"
FROM DevInfoFilteredAndSorted AS DI
WHERE DI.RN = 1


Center
Dev id
Dev Type
Latest Date


C1
111
T1
2020-01-06

C3
222
T5
2020-03-06

C2
333
T4
2020-01-06

C4
444
T6
2020-01-06

Center	Dev id	Dev Type	Latest Date
C1	111	T1	2020-01-06
C3	222	T5	2020-03-06
C2	333	T4	2020-01-06
C4	444	T6	2020-01-06