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

Navigation
7

I applied an orientation of another topic and it worked cool when you only have one record in the table with the range, 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 "INPUTS" 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 mount a single list containing all the days between these grouped intervals per person? That is: 

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 build the list containing only the distinct ones better still, otherwise I want to use the good old DISTINCT on the list dates.

I tried to adapt it as follows: 

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 it did not roll. The result comes in the same proportion of a Geometric Progression (1,2,4,8,16 ...), that is, in this example it will return:
1x 01/01/2018 , 2x 02/01/2018 , 4x 03/01/2018 , 8x 04/01/2018 and 16x 05/01/2018 for ID 1 and% 1x 03/01/2018 , 2x 04/01/2018 , 4x 05/01/2018 , 8x 06/01/2018 and 16x 07/01/2018 for ID 2 .

Thanks for the help.

    
asked by anonymous 17.05.2018 / 12:09

1 answer

2

Update:

Due to the additional explanation, where the need would be to determine the time the person stayed in the place, 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;
  

Result: 

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, list 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
  

Result: 

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 this, I believe you only have to filter by the date of the period you want to see

I've done the following:

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

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

Finally, I selected the smallest date in the range, adding the numeric sequence, thus having a list with all days within the range.

Now, just do Select with the data in the table. Here is 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
  

Result: 

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 it in SQLFiddle

    
17.05.2018 / 14:08
How to do autocomplete JqueryUi with PHP return multiple data? SQL Server JOINS vs Subquerys