First of all, an SQL fiddle with it: http://sqlfiddle.com/#!4/fe7b07/2
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
MIN(date_from); and from
For this I created the
SELECT * FROM minmax, which will select the
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
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.
SELECT * FROM dayswhich select dates out of the database
SELECT * FROM days_beforewhich will show the dates before
MIN(date_from)of query 1
SELECT * FROM days_afterfor dates after
MAX(date_from)of query 1
And in the end I'd
UNION those three queries to have them all combined.