How to list every day between two dates for more than one record (Oracle)

Question

I applied an orientation of another topic and it worked cool when you only have one record in the table with the interval, but I had problems when the same record has more than one date range.

Using the query below:

select trunc(to_date(X.DT_ENTRADA)) + (level-1) periodo
  from dual
connect BY level <= to_number(to_date(X.DT_SAIDA) - TO_DATE(X.DT_ENTRADA)) + 1

and having the table "ENTRIES" below as an example:

ID  DT_ENTRADA   DT_SAIDA     CD_PESSOA   
1   01/01/2018   05/01/2018   123        
2   03/01/2018   07/01/2018   123  
3   10/03/2018   15/03/2018   999        
4   15/03/2018   17/03/2018   999

How would I set up a single list containing every day between these intervals grouped by person? That is to say:

PERIODO      ID   CD_PESSOA  
01/01/2018   1    123   
02/01/2018   1    123   
03/01/2018   1    123   
03/01/2018   2    123   
04/01/2018   1    123   
04/01/2018   2    123   
05/01/2018   1    123   
05/01/2018   2    123   
06/01/2018   2    123   
07/01/2018   2    123

If you have a way to compile the list containing only the distinct best yet, otherwise I intend to use the good old DISTINCT on the dates of the list.

I tried to adapt it this way:

SELECT DISTINCT trunc(to_date(X.DT_ENTRADA) + (LEVEL - 1),
        X.ID
  FROM (
        SELECT Y.ID,
               Y.DT_ENTRADA,
               Y.DT_SAIDA,
               Y.CD_PESSOA
          FROM ENTRADAS Y
         WHERE Y.CD_PESSOA = 123
       ) X
CONNECT BY LEVEL <= to_number(to_date(X.DT_SAIDA) - TO_DATE(X.DT_ENTRADA)) + 1

but did not roll. The result comes in the same proportion of a Geometric Progression (1,2,4,8,16...), ie in this example will return:
1x 01/01/2018, 2x 02/01/2018, 4x 03/01/2018, 8x 04/01/2018 and 16x 05/01/2018 to the ID 1 and
1x 03/01/2018, 2x 04/01/2018, 4x 05/01/2018, 8x 06/01/2018 and 16x 07/01/2018 to the ID 2.

I appreciate the help.

Answer 1

Updating:

Due additional explanation, where the need would be to determine the time the person stayed on site, I made the following command:

with 
dias as 
(
  select (e.x + level -1)  dia
  from (select min(a.dt_entrada) x, max(a.dt_saida) y from entradas a where a.cd_pessoa = 123) e
  connect by level <= ceil(y-x)+1
), xentradas as 
(select 
d.dia,
(select LISTAGG(x.id,',') WITHIN GROUP (ORDER BY e.id) as ids from entradas x 
 where x.cd_pessoa = e.cd_pessoa 
 and (x.dt_entrada >= e.dt_entrada and x.dt_entrada <= e.dt_saida
     OR x.dt_saida >= e.dt_entrada and x.dt_saida <= e.dt_saida )) as ids_entradas,
e.cd_pessoa     
from dias d
inner join entradas e on d.dia between e.dt_entrada and e.dt_saida
)

select 
min(x.dia),
max(x.dia),
max(x.dia)- min(x.dia) as duracao_dias,
x.ids_entradas,
x.cd_pessoa
from xentradas x
group by x.ids_entradas, x.cd_pessoa;

Upshot:

MIN(X.DIA)              MAX(X.DIA)              DURACAO_DIAS    IDS_ENTRADAS    CD_PESSOA
2018-01-10T00:00:00Z    2018-01-15T00:00:00Z    5               5         123
2018-01-01T00:00:00Z    2018-01-07T00:00:00Z    6               1,2       123

Sqlfiddle

---

If you still need the result, listing all the records, as in your example:

with 
dias as 
(
  select (e.x + level -1)  dia
  from (select min(a.dt_entrada) x, max(a.dt_saida) y from entradas a where a.cd_pessoa = 123) e
  connect by level <= ceil(y-x)+1
)

select 
d.dia,
x.id,
e.cd_pessoa     
from dias d
inner join entradas e on d.dia between e.dt_entrada and e.dt_saida
left outer join entradas x on x.cd_pessoa = e.cd_pessoa 
 and (x.dt_entrada >= e.dt_entrada and x.dt_entrada <= e.dt_saida
 and x.dt_saida >= e.dt_entrada and x.dt_saida <= e.dt_saida )
order by d.dia

Upshot:

DIA                     ID  CD_PESSOA
2018-01-01T00:00:00Z    1   123
2018-01-02T00:00:00Z    1   123
2018-01-03T00:00:00Z    1   123
2018-01-03T00:00:00Z    2   123
2018-01-04T00:00:00Z    2   123
2018-01-04T00:00:00Z    1   123
2018-01-05T00:00:00Z    1   123
2018-01-05T00:00:00Z    2   123
2018-01-06T00:00:00Z    2   123
2018-01-07T00:00:00Z    2   123
2018-01-10T00:00:00Z    5   123
2018-01-11T00:00:00Z    5   123
2018-01-12T00:00:00Z    5   123
2018-01-13T00:00:00Z    5   123
2018-01-14T00:00:00Z    5   123
2018-01-15T00:00:00Z    5   123

ps. I added an ID 5 to consider the comment example.

Sqlfiddle

After that, I believe it is enough for you to filter by the date of the period you wish to consult

---

I did the following:

I selected the entire range you have in the table with a UNION, and put in a temporary table called datas. This makes it easier to get the longest and shortest date.

Then I generated a sequence of numbers, counting the difference of days between the smallest and the longest date. I saved this sequence in a temporary table called serie.

Finally, I selected the smallest date of the interval, adding the numerical sequence, thus having a list with all days within the range.

Now, just do the Select with the table data. Follow the code:

with datas as 
(
  select dt_entrada as data from entradas
  union 
  select dt_saida from entradas
), serie as 
(
  select level l
  from dual
   connect by level <= ceil(((select max(data) from datas)-(select min(data) from datas)))+1
), dias as 
(
  select 
    ((select min(data) from datas) +l-1) as dia
  from serie
)

select
d.dia,
e.id,
e.cd_pessoa
from entradas e 
left outer join dias d on d.dia >= e.dt_entrada and d.dia <= e.dt_saida   
where e.cd_pessoa = 123
order by d.dia

Upshot:

DIA                     ID  CD_PESSOA
2018-01-01T00:00:00Z    1   123
2018-01-02T00:00:00Z    1   123
2018-01-03T00:00:00Z    2   123
2018-01-03T00:00:00Z    1   123
2018-01-04T00:00:00Z    1   123
2018-01-04T00:00:00Z    2   123
2018-01-05T00:00:00Z    1   123
2018-01-05T00:00:00Z    2   123
2018-01-06T00:00:00Z    2   123
2018-01-07T00:00:00Z    2   123

I put in the Sqlfiddle