Not sure if I understand the requirement correctly but from what I gather you first need to find the rows for each empid containing max based on date2 and then from this set the rows having max of date1 per each empid.
If this was correct interpretation, you could consider something like this
First the example data
create table ta1 (
empid int,
name varchar(10),
date1 date,
date2 date
);
insert into ta1 (EmpID, Name, Date1, Date2) values
(1, 'A', '30/Jan/2021', '20/Jan/2021'),
(1, 'A', '29/Jan/2021', '20/Jan/2021'),
(2, 'B', Null, '20/Jan/2021'),
(2, 'B', NUll, '21/Jan/2021'),
(3, 'C', '28/Jan/2021', '21/Jan/2021');
Now let's get the rows having max date2 for each emp. The query could look like this
WITH Date2Query AS (
SELECT t1.empid,
t1.name,
t1.date1,
t1.date2
FROM ta1 T1
WHERE t1.date2 = (select max(t2.date2)
from ta1 T2
where t1.name = t2.name)
)
SELECT * FROM Date2Query;
so the result would be
empid name date1 date2
3 C 2021-01-28 2021-01-21
2 B NULL 2021-01-21
1 A 2021-01-30 2021-01-20
1 A 2021-01-29 2021-01-20
Now to get the rows for each emp having max of date1 you can use the approach you already tried but in case of null, use a non-existent date in the comparison to ensure that if max is null then the comparison is true. For example using COALESCE the query could look like this
WITH Date2Query AS (
SELECT t1.empid,
t1.name,
t1.date1,
t1.date2
FROM ta1 T1
WHERE t1.date2 = (SELECT max(t2.date2)
from ta1 T2
where t1.name = t2.name)
)
SELECT t1.empid,
t1.name,
t1.date1
FROM Date2Query T1
WHERE COALESCE(t1.date1,'01/01/1900')
= COALESCE((SELECT MAX(t2.date1)
FROM Date2Query T2
WHERE t1.name = t2.name),'01/01/1900') ;
And the result is
empid name date1
3 C 2021-01-28
2 B NULL
1 A 2021-01-30
This can be re-written in several ways. One option is to fetch NULL's and non-NULL's separately, for example
WITH Date2Query AS (
SELECT t1.empid,
t1.name,
t1.date1,
t1.date2
FROM ta1 T1
WHERE t1.date2 = (SELECT max(t2.date2)
from ta1 T2
where t1.name = t2.name)
)
SELECT t1.empid,
t1.name,
t1.date1
FROM Date2Query T1
WHERE t1.date1 = (SELECT MAX(t2.date1)
FROM Date2Query T2
WHERE t1.name = t2.name)
UNION ALL
SELECT t1.empid,
t1.name,
t1.date1
FROM Date2Query T1
WHERE t1.date1 IS NULL;