SQL Server 2014

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

create table #Roles
(
id int,
role_name varchar(50),
start_date datetime,
end_date datetime
)

insert into #Roles (id, role_name, start_date, end_date) values ('1','Admin','2020-12-01','2020-12-11');
insert into #Roles (id, role_name, start_date, end_date) values ('2','Admin','2020-12-12',null);
insert into #Roles (id, role_name, start_date, end_date) values ('3','Warehouse manager','2020-12-01','2020-12-09');
insert into #Roles (id, role_name, start_date, end_date) values ('4','Warehouse manager','2020-12-10','2020-12-12');
insert into #Roles (id, role_name, start_date, end_date) values ('5','Buyer','2020-12-01','2020-12-11');
insert into #Roles (id, role_name, start_date, end_date) values ('6','Buyer','2020-12-10',null);
insert into #Roles (id, role_name, start_date, end_date) values ('7','Supervisor','2020-12-01',null);
insert into #Roles (id, role_name, start_date, end_date) values ('8','Supervisor','2020-12-10',null);
insert into #Roles (id, role_name, start_date, end_date) values ('9','Sales Rep','2020-12-01','2020-12-10');
insert into #Roles (id, role_name, start_date, end_date) values ('10','Sales Rep','2020-12-10',null);
insert into #Roles (id, role_name, start_date, end_date) values ('11','Planner','2020-12-01','2020-12-09');
insert into #Roles (id, role_name, start_date, end_date) values ('12','Planner','2020-12-08','2020-12-12');

; WITH CTE AS
(
    SELECT 
        id
        , role_name
        , start_date
        , COALESCE(end_date, '2100-01-01') AS end_date
        , LAG(COALESCE(end_date, '2100-01-01')) 
             OVER (PARTITION BY role_name ORDER BY start_date) AS prev_end_date
    FROM #Roles
)
SELECT id, role_name
FROM #Roles 
WHERE role_name IN
(
    SELECT DISTINCT role_name
    FROM CTE c
    WHERE prev_end_date >= start_date
)

id	role_name
5	Buyer
6	Buyer
7	Supervisor
8	Supervisor
9	Sales Rep
10	Sales Rep
11	Planner
12	Planner