How to select dates from dual, but with joined data?

Question

I got an SQL problem I'm not capable to solve.

First of all, an SQL fiddle with it: http://sqlfiddle.com/#!4/fe7b07/2

As you see, I fill the table with some dates, which are bound to some ID. Those dates are day by day. So for this example, we'd have something like this, if we only look at January:

The timelines spanning from 2020-01-01 to 2020-01-31, the blocks are the dates in the database. So this would be the simple SELECT * FROM days output.

What I now want is to fill in some days to this output. These would span from timeline_begin to MIN(date_from); and from MAX(date_from) to timeline_end.

I'll mark these red in the following picture:

The orange span is not necessary to be added, too, but if your solution would do that too, that would be also ok.

Ok, so far so good.

For this I created the SELECT * FROM minmax, which will select the MIN(date_from) and MAX(date_from) for every id_othertable. Still no magic involved.

What I struggle is now creating those days for every id_othertable, while also joining the data they have on them (in this fiddle, it's just the some_info field).

I tried to write this in the SELECT * FROM days_before query, but I just can't get it to work. I read about the magical function CONNECT BY, which will on its own create dates line by line, but I can't get to join my data from the former table. Every time I join the info, I only get one line per id_othertable, not all those dates I need.

So the ideal solution I'm looking for would be to have three select queries:

1. SELECT * FROM days which select dates out of the database
2. SELECT * FROM days_before which will show the dates before MIN(date_from) of query 1
3. SELECT * FROM days_after for dates after MAX(date_from) of query 1

And in the end I'd UNION those three queries to have them all combined.

I hope I could explain my problem good enough. If you need any information or further explaining, please don't hesitate to ask.

---

EDIT 1: I created a pastebin with some example data: https://pastebin.com/jskrStpZ

Bear in mind that only the first query has actual information from the database, the other two have created data. Also, this example output only has data for id_othertable = 1, so the actual query should also have the information for id_othertable = 2, 3.

EDIT 2: just for clarification, the field date_to is just a simple date_from + 1 day.

Answer 1

If you have denormalised date it's quite simple:

with bas as (
    select 1 id_other_table, to_date('2020-01-05', 'YYYY-MM-DD') date_from, to_date('2020-01-06', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-06', 'YYYY-MM-DD') date_from, to_date('2020-01-07', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-07', 'YYYY-MM-DD') date_from, to_date('2020-01-08', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-10', 'YYYY-MM-DD') date_from, to_date('2020-01-11', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-11', 'YYYY-MM-DD') date_from, to_date('2020-01-12', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-12', 'YYYY-MM-DD') date_from, to_date('2020-01-13', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-10', 'YYYY-MM-DD') date_from, to_date('2020-01-11', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-11', 'YYYY-MM-DD') date_from, to_date('2020-01-12', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-12', 'YYYY-MM-DD') date_from, to_date('2020-01-13', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-20', 'YYYY-MM-DD') date_from, to_date('2020-01-21', 'YYYY-MM-DD') date_to, 'friend' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-21', 'YYYY-MM-DD') date_from, to_date('2020-01-22', 'YYYY-MM-DD') date_to, 'friend' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-22', 'YYYY-MM-DD') date_from, to_date('2020-01-23', 'YYYY-MM-DD') date_to, 'friend' some_info from dual)
, ad as (select trunc(sysdate,'YYYY') -1 + level all_dates from dual connect by level <= 31)
select distinct some_info,all_dates from bas,ad where (some_info,all_dates) not in (select some_info,date_from from bas)

If you have longer date ranges or mind of the time the query needs another solution is helpful. But that is harder to debug. Because it's quite hard to get the orange time slot