Select with re occurrences of a period record

Question

Select with re occurrences of a period record

Navigation

#1 by (0 votes)

1

I need to know if a record has occurred again in a 7-day period. For this, I have a table that saves the records and the date of occurrence. 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 enter a start date and an end date, for example: Records between 3/1 and 3/31.

I need the result to indicate 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 a maximum of 7 days. At the moment I'm using the following code, which can only bring me in the period how many times the same record has occurred but does not have the 7 day criteria.

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

oracle select count

asked by anonymous 04.05.2017 / 16:14

1 answer

Error in Revolution Slider Random Ads Between Cells in a Table View

score 0 · Answer 1

You can not do what you are asking for by using only a simple query using SELECT .

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

Below is an example with explanatory comments on how to make a extraction based on the information you 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 any specific problems open another question.

Learn more about PL / SQL by reading Oracle documentation . / p>

Note that the following snippet is just for mounting a table with some example records, if you want you can change this to better fit the actual case, or use the actual 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_;