SQL Server 2019

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

create table position_history (position varchar(100), person varchar(100), inserted date, deleted date);
insert into position_history values('Sales 1','Mark','2019-01-01','2019-02-01');
insert into position_history values('Sales 2','Mark','2019-02-01','2019-05-01');
insert into position_history values('Sales 3','Mark','2019-05-01','2020-07-01');
insert into position_history values('Senior Daveloper','Peter','2019-01-01','2020-07-01');
insert into position_history values('Junior Developer','John','2019-05-01','2019-04-01');
insert into position_history values('PM','John','2019-04-01','2020-07-01');


6 rows affected

select mt.position, mt.person, dt.filter_date from (SELECT '2019-03-01'  as filter_date
UNION
SELECT '2019-04-01'
UNION 
Select '2019-05-01') dt
Join
(SELECT Position, Person, inserted, deleted FROM Position_History) mt
ON dt.filter_date between mt.inserted and mt.deleted;


position
person
filter_date


Sales 2
Mark
2019-03-01

Senior Daveloper
Peter
2019-03-01

Sales 2
Mark
2019-04-01

Senior Daveloper
Peter
2019-04-01

PM
John
2019-04-01

Sales 2
Mark
2019-05-01

Sales 3
Mark
2019-05-01

Senior Daveloper
Peter
2019-05-01

PM
John
2019-05-01

position	person	filter_date
Sales 2	Mark	2019-03-01
Senior Daveloper	Peter	2019-03-01
Sales 2	Mark	2019-04-01
Senior Daveloper	Peter	2019-04-01
PM	John	2019-04-01
Sales 2	Mark	2019-05-01
Sales 3	Mark	2019-05-01
Senior Daveloper	Peter	2019-05-01
PM	John	2019-05-01