Capturar o periodo logado em uma tabela SQL

11/07/2023

Bom dia!

Preciso achar a soma do per�odo logado e o per�odo deslogado.

na tabela tenho as seguintes informa��es:

Id | Agente | Campanha | Login | Logout
1 123 266 2023-07-01 08:09:50 2023-07-01 12:34:32
2 123 327 2023-07-01 08:09:50 2023-07-01 12:34:32
3 123 90 2023-07-01 10:00:03 2023-07-01 11:20:09
4 123 266 2023-07-01 12:34:59 2023-07-01 17:50:04
5 123 327 2023-07-01 12:34:59 2023-07-01 18:07:50
6 123 249 2023-07-01 15:00:01 2023-07-01 17:00:08
7 123 327 2023-07-01 18:08:20 2023-07-01 18:33:39

a campanha � irrelevante, mas coloquei para demonstrar o porque temos v�rios per�odos, exemplo:
no ID 1 e 2 , o login foi das 8 as 12 pois as campanhas iniciaram simultaneamente;
no ID 3 ocorreu um login dentro do per�odo do ID 1 e 2. (neste caso, deveria ser desconsiderado, pois ela j� estava logado no per�odo dos ID's 1 e 2).

com base nisso, precisava pegar:
(ultimo login) - (primeiro login): 10:23:49

Per�odo deslogado: 00:00:57
Per�odo logado: 10:22:52

Mauricio Daniel

Curtir t�pico + 0

Responder

Post mais votado

11/07/2023

�timo exerc�cio para raciocinar.

Temos uma informa��o (1), que corresponde aos per�odos individuais, que podem se sobrepor.

A primeira tarefa para resolver este problema � resolver as intersec��es. Ent�o, pensei no seguinte algoritmo:

Ao inv�s de considerarmos cada per�odo individual, vamos criar um conjunto de hor�rios de in�cio e fim de per�odo independentes, que chamaremos de conjunto (2). O resultado final esperado � que tenhamos uma sequ�ncia de hor�rios que deve representar, alternadamente, as entradas e sa�das de per�odos, podendo iniciar com a entrada de um per�odo e sa�da de outro, caso eles se sobreponham.

Para construir esse conjunto (2), podemos fazer:

select agente, 'I' tipo, login horario from tabela
union all
select agente, 'F' tipo, logout horario from tabela

Em seguida, comparamos cada hor�rio do conjunto (2) com os per�odos do conjunto (1), para eliminar os pontos contidos no per�odo. Como um per�odo pode terminar no mesmo hor�rio que o in�cio de outro, n�o vamos eliminar pontos que coincidam com as extremidades do per�odo. Mas, ao final, vamos retornar unicamente os hor�rios de cada tipo ("I"n�cio, "F"im), para que per�odos com in�cio ou fim repetidos apare�am apenas uma vez.

with
  horarios as
   (select agente, 'I' tipo, login horario from tabela
    union all
    select agente, 'F' tipo, logout horario from tabela)
select distinct h.agente, h.tipo, h.horario
from horarios h
where
  not exists (select 1
              from tabela t
              where
                h.agente = t.agente and
                h.horario > t.login and
                h.horario < t.logout)

Neste ponto temos, para cada agente, uma sequ�ncia de per�odos consecutivos. A cada 2 hor�rios (inicio/fim), teremos um per�odo logado. A cada 2 hor�rios (fim/inicio), teremos um per�odo deslogado.

Precisamos novamente recriar os per�odos (na mesma linha):

with
  horarios as
   (select agente, 'I' tipo, login horario from tabela
    union all
    select agente, 'F' tipo, logout horario from tabela),
  horarios_unicos as
   (select distinct h.agente, h.tipo, h.horario
    from horarios h
    where
      not exists (select 1
                  from tabela t
                  where
                    h.agente = t.agente and
                    h.horario > t.login and
                    h.horario < t.logout)),
  periodos as
   (select
      agente,
      tipo,
      horario inicio,
      lead(horario) over(partition by agente order by horario) fim
    from horarios_unicos),
select
  agente,
  sum(case when tipo = 'I' then datediff(second, inicio, fim) else 0 end) tempo_logado,
  sum(case when tipo = 'F' then datediff(second, inicio, fim) else 0 end) tempo_deslogado
from periodos
group by agente

Neste ponto voc� ter� a rela��o de agentes e o tempo em segundos que cada um passou logado, bem como o tempo em segundos que cada um passou deslogado entre os per�odos.

Para retornar em formato 'hh:mm:ss' pode ser necess�rio fazer alguma matem�tica. Mas o resultado deve estar correto.

Arthur Heinrich

Responder

Gostei + 1