Capturar o periodo logado em uma tabela SQL

SQL Server

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

Mauricio Daniel

Curtidas 0

Melhor post

Arthur Heinrich

Arthur Heinrich

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.
GOSTEI 1
POSTAR