By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (
id number(10),
start_date timestamp,
end_date timestamp);
insert into test values(1, to_date('01.01.2019', 'dd.mm.yyyy'), to_date('03.01.2019', 'dd.mm.yyyy'));
1 rows affected
insert into test values(2, to_date('02.01.2019', 'dd.mm.yyyy'), to_date('05.01.2019', 'dd.mm.yyyy'));
1 rows affected
insert into test values(3, to_date('04.01.2019', 'dd.mm.yyyy'), to_date('06.01.2019', 'dd.mm.yyyy'));
1 rows affected
SELECT dt.mydate, COUNT(DISTINCT id) cnt
FROM
( SELECT DISTINCT start_date mydate FROM test UNION ALL SELECT DISTINCT end_date FROM test ) dt
LEFT JOIN test t ON dt.mydate >= t.start_date AND dt.mydate <= t.end_date
GROUP BY dt.mydate
ORDER BY dt.mydate
| MYDATE | CNT |
|---|---|
| 01-JAN-19 12.00.00.000000 AM | 1 |
| 02-JAN-19 12.00.00.000000 AM | 2 |
| 03-JAN-19 12.00.00.000000 AM | 2 |
| 04-JAN-19 12.00.00.000000 AM | 2 |
| 05-JAN-19 12.00.00.000000 AM | 2 |
| 06-JAN-19 12.00.00.000000 AM | 1 |