By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table desks
(
start_date date,
stop_date date,
id varchar(10),
desk_number int
);
insert into desks values
('2020/01/20', '2020/05/20', '0100', 55),
('2020/03/20', '2020/06/20', '0100', 56),
('2020/02/22', '2020/04/22', '0200', 91);
3 rows affected
create table teams
(
start_date date,
stop_date date,
id varchar(10),
team_number int
);
insert into teams values
('2020/01/20', '2020/04/20', '0100', 2),
('2020/02/20', '2020/06/20', '0100', 3),
('2020/02/22', '2020/04/22', '0200', 8);
3 rows affected
SELECT CASE
WHEN teams.start_date > desks.start_date
THEN teams.start_date
ELSE desks.start_date
END start_date
, CASE
WHEN teams.stop_date < desks.stop_date
THEN teams.stop_date
ELSE desks.stop_date
END stop_date
, desks.id
, desks.desk_number
, teams.team_number
FROM desks
JOIN teams
ON desks.start_date < teams.stop_date
AND teams.start_date < desks.stop_date
AND teams.id = desks.id
ORDER BY desks.id, 1
| start_date | stop_date | id | desk_number | team_number |
|---|---|---|---|---|
| 2020-01-20 | 2020-04-20 | 0100 | 55 | 2 |
| 2020-02-20 | 2020-05-20 | 0100 | 55 | 3 |
| 2020-03-20 | 2020-06-20 | 0100 | 56 | 3 |
| 2020-03-20 | 2020-04-20 | 0100 | 56 | 2 |
| 2020-02-22 | 2020-04-22 | 0200 | 91 | 8 |