Select with re occurrences of a period record

Question

I need to know if a record occurred again in a 7-day period. For this, I count with a table that saves the records and the occurrence date. For example:

Registro: x | Data: 13/03/2017
Registro: y | Data: 14/03/2017
Registro: x | Data: 17/03/2017
Registro: x | Data: 21/03/2017
Registro: z | Data: 31/03/2017

For this, the user would inform an initial date and an end date, for example: Records between 01/03 and 31/03.

I need the result to tell me how many times in the period the record was repeated using the criterion that the difference in days from one to the other should be no more than 7 days. At the moment I am using the following code, which only manages to bring me in the period how many times the same record occurred, but does not have the criterion of 7 days.

SELECT codigo, data FROM aviso
WHERE data IS NOT NULL
AND data BETWEEN To_Date('01/03/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AND To_Date('31/03/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
--HAVING Count(*) > 1
ORDER BY codigo

Answer 1

Can’t do what you’re asking using just a simple query using SELECT.

You need to map all rules involved and use the PL/SQL to extract only the data you want.

Below is an example with explanatory comments on how to make an extract based on the information he gave:

CREATE GLOBAL TEMPORARY TABLE registros --tabela temporária para adicionar os registros a serem exibidos
   ( registro CHAR(1),
     dtabertura DATE))
   ON COMMIT PRESERVE ROWS;

begin
   Declare Cursor cursorRegs is
           WITH tabela_(registro, data) as ( --tabela criada apenas para montar o exemplo
            select 'x', to_date('13/03/2017', 'DD/MM/YYYY') from dual union all
            select 'y', to_date('14/03/2017', 'DD/MM/YYYY') from dual union all
            select 'x', to_date('17/03/2017', 'DD/MM/YYYY') from dual union all
            select 'x', to_date('21/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('13/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('19/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('25/03/2017', 'DD/MM/YYYY') from dual union all
            select 'z', to_date('31/03/2017', 'DD/MM/YYYY') from dual )
            select * from tabela_; --selecionado todos os dados da tabela
   --declare aqui as váriaveis que você for utilizar
   aData DATE;
   oRegistroAtual VARCHAR2(50);
   --Se necessário, adicione outras variáveis aqui

   Begin
     For reg in cursorRegs Loop --Para cada registro selecionado
       --Mapeie toda a lógica envolvida e aqui filtre qual registro deve ou não ser inserido na tabela temporária
       --Exemplo:
       oData := reg.data;
       oRegistroAtual := reg.registro;

       Declare Cursor registrosAdicionar is
            WITH tabela_(registro, data) as (
            select 'x', to_date('13/03/2017', 'DD/MM/YYYY') from dual union all
            select 'y', to_date('14/03/2017', 'DD/MM/YYYY') from dual union all
            select 'x', to_date('17/03/2017', 'DD/MM/YYYY') from dual union all
            select 'x', to_date('21/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('13/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('19/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('25/03/2017', 'DD/MM/YYYY') from dual union all
            select 'z', to_date('31/03/2017', 'DD/MM/YYYY') from dual )
            --selecionado apenas os registros que ocorreram entra data e data+7
            select * from tabela_ where registro = oRegistroAtual and data between reg.data and reg.data+7;

        For regAdd in registrosAdicionar Loop
            --aqui será percorrido o segundo cursor adicionando os valores na tabela temporária
            INSERT INTO registros values (regAdd.registro, regAdd.data);
            --Talvez aqui você precise incrementar sua lógica, pois alguns dados serão inserido de forma repetida.
            --Essa tabela é apenas um exemplo, mas no caso real você pode usar a PK da tabela e verificar se esse caso específico já existe na temp table
        End Loop;
     End Loop;   
   End;
end;

After executing the code make a select * in the temporary table:

select * from registros;

Note that I have not tested this code. It’s just a base for you to start your extraction.

During development if you have a specific problem open another question.

Learn more about PL/SQL by reading oracle documentation.

Note that the excerpt below is only to mount a table with some example records, if you want to change this to better adapt to the real case, or use the real table.

WITH tabela_(registro, data) as ( 
            select 'x', to_date('13/03/2017', 'DD/MM/YYYY') from dual union all
            select 'y', to_date('14/03/2017', 'DD/MM/YYYY') from dual union all
            select 'x', to_date('17/03/2017', 'DD/MM/YYYY') from dual union all
            select 'x', to_date('21/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('13/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('19/03/2017', 'DD/MM/YYYY') from dual union all
            select 'w', to_date('25/03/2017', 'DD/MM/YYYY') from dual union all
            select 'z', to_date('31/03/2017', 'DD/MM/YYYY') from dual )
            select * from tabela_;

Answer 2

I believe what you’re looking for is:

SELECT a.codigo, a.data FROM aviso a
    WHERE a.data IS NOT NULL
    AND a.data BETWEEN To_Date('01/03/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS') AND To_Date('31/03/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
    AND EXISTS (SELECT * FROM aviso b WHERE b.data <= (a.data + 7))
    ORDER BY codigo